Simulate a Lookup function in SQL

I have a table on sheet1 that has over 300K rows of dollar values  in MS Excel.  On Sheet2 I have a have a second table  I s use for a lookup  and that contains an amount and a revenue band:
amt      BAND
0      UNDER 1K
1000      $1k>5K
5000      $5K > $10K
10000      $10 > $25K
25000      $25K > $50K
50000      $50K > $100K
100000      $100K > $500K
500000      $500 > $1MILL
1000000      $1MILL+
 
I use this Lookup table  to place a revenue band value in the  first table: =Lookup (A2, Sheet2!A$2:A$10, B$2, B$10)

--so that the result in sheet one would look something like:
A1               B1
AMT            BAND
1000         $1k>5K
27012       $25K > $50K

This works fine in Excel , except that it tales al ong time to render at at times crashes. Is ther a way to simulate this in SQL, with two tables-- a values table and a lookup table?

Thanks!!
Wm Allen SmithAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
I would do this in a case statement:

select 
   case 
   when amount < 1000       then cast( 'UNDER 1K' as varchar(100))
   when amount < 5000      then cast( ' $1k>5K' as varchar(100))
   when amount < 10000     then cast(  '$5K > $10K' as varchar(100))
   ...
   else cast(  '  $1MILL+' as varchar(100)) 
   end    as  Band


   

Open in new window

0
Randy PooleCommented:
In SQL you would just need a single table, but any reason you just don't make a VBA function to do this?  Or does your lookup sheet change often?
0
aikimarkCommented:
The preferred approach in SQL will be to have a table with both lower and upper bound values for the ranges as well as the BAND label text (see below)
LowAmt	HiAmt	BAND
0	999	UNDER 1K
1000	4999	$1k>5K
5000	9999	$5K > $10K
10000	24999	$10 > $25K
25000	49999	$25K > $50K
50000	99999	$50K > $100K
100000	499999	$100K > $500K
500000	999999	$500 > $1MILL
1000000	 999999999999999 	$1MILL+

Open in new window

You can join another table to this one using a Between clause.
Example:
Select MyTable.*, BandLookup.BAND 
From MyTable, BandLookup
Where MyTable.Amt Between BandLookup.LowAmt and BandLookup.HiAmt

Open in new window

It is also possible to have an open-ended range, but your SQL will need to accommodate it, perhaps with a UNION query or IIF()/CASE

Are you using Access or SQL Server (or similar) database?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
I've also used an ADODB recordset object to do a join of Excel worksheet data and populate a range using the CopyFromRecordset method.  All of this was in the Excel VBA run-time environment.
0
Wm Allen SmithAuthor Commented:
Perfect!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.