how to use vlookup from 2 lookup value cell

Question

i have a table of names with their numbers and want to use vlookup formula to lookup a specific number from a cell from that table and return me the name as the result but if the specific number in that cell is not found then i want it to try another cell for example

Table has 2 column

Note employee code consist of 3 numbers and station number consist of 4 numbers

| Employee Code/Station Number # |  Name  |
|:------------------------------:|:------:|
| 123                            |  John  |
| 456                            | joseph |
| 3213                           |  jack  |
| 5656                           |  John  |

On the next sheet for the result I have 3 columns

| Employee Code | Employee Station No | Name |
|---------------|---------------------|------|
| 456           |                     |      |
|               | 3213                |      |
|               | 5656                |      |
| 123           |                     |      |

Now I need a vlookup formula which will first lookup in the employee code column if number is there then substitute the result but if employee code is empty then lookup in the employee station no and substitute the result

=IFERROR(VLOOKUP(*employee code*, *TABLE*,2,false),VLOOKUP(*employee station no*, *TABLE*,2,false)

Welcome to SO! When you place a question try to add a minimum content: input sample, expected output sample, what did you try, research and where are you stuck. If you don´t provide oone input sample and your expected output, you will get "generic" solutions. — David García Bodego

David García Bodego David García Bodego · Accepted Answer · 2019-10-28T07:35:23

Considering that the first table is "Sheet1" and the second table is "Sheet2" and the first cell on both tables is A1, try this one. (If not, just modified according to your data)

=IF(AND(A2<>"",B2=""),VLOOKUP(A2,Sheet1!$A$12:$B$15,2,FALSE),IF(AND(A2="",B2<>""),VLOOKUP(B2,Sheet1!$A$12:$B$15,2,FALSE),"Check Data!!"))

The initial condition will check if there is value (any) in the Employee code but no Station and you will get your VLOOKUP there, if it is not the case, it will search by station and not employee. In the remaining scenarios (no data or Station and employee data) an error message is shown.

EDIT

If you want to receive a value when both cells have value then use:

=IF(A2<>"",VLOOKUP(A2,Sheet1!$A$12:$B$15,2,FALSE),IF(B2<>"",VLOOKUP(B2,Sheet1!$A$12:$B$15,2,FALSE),"Check Data!!"))

Hope it helps!

how to use vlookup from 2 lookup value cell

1 Answers