vlookup to the left of reference

Question

vlookup to the left of reference

Anonymous

I have a vlookup I use to copy matching data from Sheet1 to my sheet called LastMile. My only problem there is data in 2 columns before my reference column. These are the vlookups I have. What type of formula would I use to get the data in column C then get the data in column D.

Column C's data would go to column J and Column D would go to column K in Last

=IFERROR(VLOOKUP(G2,'Sheet1'!$E:T,3,False),"")

=IFERROR(VLOOKUP(G2,'Sheet1'!$E:T,4,False),"")

=IFERROR(VLOOKUP(G2,'Sheet1'!$E:T,5,False),"")

=IFERROR(VLOOKUP(G2,'Sheet1'!$E:T,10,False),"")

=IFERROR(VLOOKUP(G2,'Sheet1'!$E:T,13,False),"")

=IFERROR(VLOOKUP(G2,'Sheet1'!$E:T,18,False),"")

=IFERROR(VLOOKUP(G2,'Sheet1'!$E:T,20,False),"")

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

1 additional answer

Answer 1

Change those two to

=IFERROR(INDEX(Sheet1!C:C,MATCH(G2,Sheet1!E:E,FALSE)),"")

=IFERROR(INDEX(Sheet1!D:D,MATCH(G2,Sheet1!E:E,FALSE)),"")

Note that your formulas

=IFERROR(VLOOKUP(G2,'Sheet1'!$E:T,18,False),"")

=IFERROR(VLOOKUP(G2,'Sheet1'!$E:T,20,False),"")

Will always return "" since E:T is only 16 columns, and the 18 and 20 need to be the column number within the referenced range - 14 and 16 for these two formulas, apparently. (All your formulas may be wrong....) You may benefit from changing them all to, for getting data from column T, for example

=IFERROR(INDEX(Sheet1!T:T,MATCH(G2,Sheet1!E:E,FALSE)),"")

or use XLOOKUP (if it is available in your version), like so for the same data as the previous formula:

=XLOOKUP(G2,Sheet1!E:E,Sheet1!T:T,"",FALSE)

Answer 2

Anonymous

Thank you for your help. This has saved me a ton of time.

0 comments

Share via

vlookup to the left of reference

1 additional answer