# VLOOKUP Equivalent in SQL

• Hi there

I have two tables, one with an ID and Amount (Table 1) and the Second with a Column called Variable with an entry called "Amount". This "Amount" has different values in the columns labelled 1,2,3,4 and 5 (Table 2).

How do I create a new column in a new Table (Table 3) with columns ID and Amount, that brings into the Amount column for each ID: a 1 if the Amount in Table 1 is found in a range of values that are in columns 1 and 2 (between the values in columns 1 and 2 in Table 2), that brings in a 2 if the Amount in Table 1 is found in a range of values that are in columns 2 and 3 (between the values in columns 2 and 3 in Table 2) , that brings in a 3 if the Amount in Table 1 is found in a range of values that are in columns 3 and 4 (between the values in columns 3 and 4 in Table 2), that brings in a 4 if the Amount in Table 1 is found in a range of values that are in columns 4 and 5 (between the values in columns 4 and 5 in Table 2) and brings in a 5 if the amount in Table 1 is greater than the value in Column 5 corresponding to the "amount" row in Table 2.

Any help will be much appreciated. Thank you

• A case statement should do it:

`    -- Change operators appropriately if not inclusive    CASE WHEN Amount >= Column1 AND Amount <= Column2 THEN 1         WHEN Amount >= Column2 AND Amount <= Column3 THEN 2         WHEN Amount >= Column3 AND Amount <= Column4 THEN 3         WHEN Amount >= Column4 AND Amount <= Column5 THEN 4         END-- between syntax will work if inclusive:    CASE WHEN Amount BETWEEN Column1 AND Column2 THEN 1         WHEN Amount BETWEEN Column2 AND Column3 THEN 2         WHEN Amount BETWEEN Column3 AND Column4 THEN 3         WHEN Amount BETWEEN Column4 AND Column5 THEN 4         END`

• `SELECT t1.ID, t1.Amount, t2.Amount_VLOOKUPFROM table1 t1CROSS APPLY (    SELECT CASE        WHEN t1.Amount < t2.col1 THEN 0        WHEN t1.Amount >= t2.col1 AND t1.Amount < t2.col2 THEN 1        WHEN t1.Amount >= t2.col2 AND t1.Amount < t2.col3 THEN 2        WHEN t1.Amount >= t2.col3 AND t1.Amount < t2.col4 THEN 3        WHEN t1.Amount >= t2.col4 AND t1.Amount < t2.col5 THEN 4        WHEN t1.Amount >= t2.col5 THEN 5         END AS Amount_VLOOKUP            FROM table2 t2) AS t2`

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

• If you include the Vlookup amount in your second table, you won't even require a case statement.

`declare @Table1 table (ID int identity(1,1) primary key, Amount int)	insert into @Table1 	Values (75),(22), (0),(105),(6),(11)declare @Table2 table (Col1 int primary key, Col2 int, AmountVLookup tinyint)	insert into @table2	values	(-999999999,1, 0)			,(1,10,1)			,(11,30,2)			,(31,50,3)			,(51,100,4)			,(101,999999999,5)select t1.Id, t1.Amount, t2.AmountVLookupfrom @Table1 t1join @Table2 t2 on t1.Amount >= Col1 and t1.Amount <= Col2`

A SQL JOIN is analogous to an Excel VLookup.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

• Thank you so much, this almost worked but I am only getting values of '5' for Amount_VLOOKUP. Any way of fixing this?

• Actually I managed to fix it by rearranging the values for t2! Thank you so much for your help!

Viewing 6 posts - 1 through 6 (of 6 total)