Menu
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
279
Excel Online vlookup to return multiple columns You can just insert the formula in one cell and press Enter => the matching values for the columns specified in the formula will be populated automatically.
The VLOOKUP function can be combined with other functions such as the Sum, Max, or Average to calculate values in multiple columns. As this is an array formula, to make it work we simply need to press CTRL+SHIFT+ENTER at the end of the formula.
With this formula, you may choose the entire table_array and consistently use VLOOKUP to find values in non-adjacent columns as we have found a formula to determine the column to be looked up. I was using this formula between worksheets as well, and I just got it to work.
=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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
