Simulate a Lookup function in SQL

Posted on 2014-09-03
Last Modified: 2014-09-07
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?

Question by:wasmithpfs
    LVL 39

    Expert Comment

    by:Kyle Abrahams
    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

    LVL 21

    Expert Comment

    by:Randy Poole
    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?
    LVL 44

    Accepted Solution

    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?
    LVL 44

    Expert Comment

    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.

    Author Closing Comment


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now