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.
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)