CODEWITHSUNDEEP
excelexcel-formulavlookup
Jun Dolor
Jun Dolor

Reputation: 639

How do I change the reference column in Excel vlookup

I want to change the reference column in the Excel vlookup from the first to any column. Here is my lookup table

Mon    PC Sales    Mobile Sales
Jan    800         700
Feb    500         750
Mar    600         860

My lookup values are PC Sales: 800 and Mobile Sales 860. Now I want to get their corresponding months, which is in the first column

Upvotes: 0

Views: 684

Answers (1)

VBasic2008
VBasic2008

Reputation: 54838

VLOOKUP vs XLOOKUP

Microsoft Office Support

VLOOKUP
... Remember that the lookup value should always be in the first column in the range for VLOOKUP to work correctly. For example, if your lookup value is in cell C2 then your range should start with C. ...

XLOOKUP
... Use the XLOOKUP function when you need to find things in a table or a range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from the same row in another column, regardless of which side the return column is on. ...
... XLOOKUP is different from VLOOKUP in that it uses separate lookup and return arrays, where VLOOKUP uses a single table array followed by a column index number. ...

Conclusion

  • You cannot use VLOOKUP for the requested tasks.

Either

=XLOOKUP(800,$B:$B,$A:$A)
=XLOOKUP(860,$C:$C,$A:$A)

Or

If you don't have XLOOKUP, you can use INDEX with MATCH:

=INDEX($A:$A,MATCH(800,$B:$B,0))
=INDEX($A:$A,MATCH(860,$C:$C,0))

Upvotes: 1

Related Questions