[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

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?

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

   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

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?
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.
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?
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.
wasmithpfsAuthor Commented:

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now