Lookup using table with separated columns

问题:

I would like to use VLOOKUP() using a table input consisting of columns that are not next to each other.

Example

MyCell = VLOOKUP(A1, MyTable, 2, FALSE)
MyTable = B1:B10 and D1:D10

Is there a way of making this operation work using a lookup function or any other worksheet function? Now I get an error.

Thanks in advance.

Edit: It's because the table is very large and I'm afraid that the operation will slow down the application if I use the entire table as input

回答1:

=INDEX(D1:D10,match(A1,B1:B10,0))

index/match is typically more efficient than vlookup anyway. if you really want VLOOKUP for just those columns you can use

=VLOOKUP(A1,CHOOSE({1,2},B1:B10,D1:D10),2,FALSE)

回答2:

In Google Sheets you can use array literals to concatenate two columns:

= VLOOKUP(A1, {B1:B10,D1:D10}, 2, FALSE)