excelexcel-formula

Excel VLOOKUP for 3 columns


I'm going to use the VLOOKUP for 3 columns in my xls file. Below is the reference table :

Building     |    Floor    |     Location ID
----------------------------------------------
Building A   | 1           | 1001
Building A   | 2           | 1002
Building B   | Ground      | 2000

How should I write the VLOOKUP formula for this case ? So that I can use the Building and Floor columns as the LOOKUP VALUE and the Location ID as the RESULT ? Thx.


Solution

  • I don't know the "right" way to do this, but I usually add another column that concatenates the cells I want to lookup separated by a "|" and then do the vlookup on that column.

    Building|Floor    Building    Floor    LocationID
    Building A|1      Building A  1        1001
    ....
    

    Use something like this to get the concatenated values

    =Concatenate(B2,"|",C2)
    

    You can then do your vlookup like this:

    =VLookup(Concatenate([buildingvalue],"|",[floorvalue]),A:D,4,false)