Excel VLOOKUP by second column using table name as range

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

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.

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.