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_VLOOKUP
    FROM table1 t1
    CROSS 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.AmountVLookup
    from @Table1 t1
    join @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)

You must be logged in to reply to this topic. Login to reply