Lookup using table with separated columns

Question

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

did you test if there is any efficiency problem when you apply your function for that large area of data?? in my opinion there shouldn't be problem to use VLOOKUP for continuous range even it's large one... — Kazimierz Jawor, Jul 01 '13 at 11:55

score 17 · Accepted Answer · answered Jul 01 '13 at 11:56

17

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

answered Jul 01 '13 at 11:56

JosieP

1

The choose tip helped me too!Great tip! – Sotiris Zegiannis Oct 27 '17 at 10:37

score 2 · Answer 2 · answered Feb 29 '16 at 22:47

2

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

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

answered Feb 29 '16 at 22:47

Phrogz

2 Answers2