Vlookup from two cells

[Bopo2]

Hey guys

Basically I have a vlookup formula which simply returns data based on the input in A1 (an ID number)

=VLOOKUP(A1, CustomerRecords, 2,FALSE)

However the user might know the name of customer, and not the ID, therefore I want another optional cell which will still lookup the data, however I don't know if it's possible to do this all in 1 cell, he's some pseudo style code:

=IF A1 <> "" =VLOOKUP(A1, CustomerRecords, 2,FALSE) else
=VLOOKUP(B1, CustomerRecords, 1,FALSE)

Wait... that doesn't make logical sense either really, hopefully you understand what I'm trying to achieve :doh:

[Bob Phillips]

It sounds simple enough, but an example workbook would help.

[Bopo2]

Okay I quicky made a dummy workbook, basically if the company name is entered rather than the customer no for example, the data should still appear in the same cells as they did using the customer no.

2232

Thanks


EDIT: also notice that I've named the customer rows CustomerRecords, how do you scale the range of this so that when several customer records are added, they are counted within the CustomerRecords range.

[Bob Phillips]

Try

=INDEX(INDEX(CustomerRecords,0,3),MATCH(IF($B$11<>"",$B$11,$B$12),IF($B$11<>"",INDEX(CustomerRecords,0,1),INDEX(CustomerRecords,0,2)),0))

etc.

[Bopo2]

Thanks! Just wondering if you have a spare moment to elaborate on exactly what's happening, as atm, I'm simply changing different cell addesses and moving the formula to different cells to try and reserve engineer it, sadly it's a slow process heh.

[Bob Phillips]

Firstly, the internal IF checks if there is an id or not, if so it returns column 1 else it returns column 2. This result is derived by using a INDEX statment that looks into the appropriate column (1 or 2) of the range CustomerRecords. MATCH is used to get the index of the row for the lookup value in that determined column.

The other inner INDEX statements is used to get the result column in the range CustomerRecords, namely column 3, and the outer INDEX is used with this column and the MATCH index to get a result.