CODEWITHSUNDEEP
excelexcel-formulavlookup
karamell
karamell

Reputation: 713

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

Upvotes: 15

Views: 27113

Answers (2)

Phrogz
Phrogz

Reputation: 303490

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

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

Upvotes: 3

JosieP
JosieP

Reputation: 3410

=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)

Upvotes: 18

Related Questions