24

Using the example table below, I can use the formula =VLOOKUP("ABC123456",Table1,3,FALSE) to lookup the Demand value, but I want to do be able to perform the lookup by using the Cust Part field without having to make the Cust Part field the first column in the table. Making Cust Part the first column isn't an acceptable solution, because I also need to perform lookups using the Part field, and I don't want to use hard coded ranges (e.g. $B$2:$C$4) mostly as a matter of preference, but also because using table and field names makes the formula easier to read. Is there any way to do this?

enter image description here

Oliver Salzburg
  • 89,072
  • 65
  • 269
  • 311
Drew Chapin
  • 6,270

4 Answers4

21

It's possible to use OFFSET to return the Table1 range but 1 column over, e.g.

=VLOOKUP("AZ12345",OFFSET(Table1,0,1),2,FALSE)

That will look up AZ12345 in the CustPart column and return the value from the next column

barry houdini
  • 11,212
17

You can combine INDEX and MATCH to acheive the same result of VLOOKUP without the comparison being restrained to the first column. Though it is slightly more complex. 

=INDEX(Table1[Demand],MATCH("AZ12345",Table1[Cust Part],0))

Basically, you're using MATCH to find the row number, and INDEX to get the value.

Note: Unlike VLOOKUP, if the result is a blank cell, INDEX will return 0 instead of a blank string.

Drew Chapin
  • 6,270
2

How about something like:

=VLOOKUP("ABC123456";Table1[[Cust Part]:[Demand]];COLUMNS(Table1[[Cust Part]:[Demand]]);FALSE)

I prefer this so that you can see what you are doing, even in more complex tables, plus if the structure of the table changes, the formula will still work, as long as the Cust Part column is in front of the Demand column.

Peter
  • 21
0

This is cheating, but its a low-brow solution to the problem that I implemented many years ago before I learned about any of these other solutions.

What I did was created a fourth column on the right end, with the same contents as the first.

Then I did a simple VLOOKUP using the sub-table that began with the second column and ended with the fourth. The code, therefore, is trivial.

Then you can hide the fourth column from view, if you wish.

CogitoErgoCogitoSum
  • 111