How can we look-up values from left in Excel?

lookup
excel

#1

Hi,

From below reference table, i want to extract Marks based on ID number. Here Function Vlookup is not working because it returns output from right side of key field and to work with vlookup, i need to change the order of the columns but i do not have access to change it. Can you suggest the way to lookup values from left side?

Reference Table

Marks      Subject        ID
65             Math           A005
75             Math           A006
74             Math           A003

Thx,
Mukesh


#2

@Mukesh
We can perform this in various ways. I am doing it with most famous and used method. The combination of INDEX and MATCH function.

INDEX:- Returns the value or reference of a cell at the intersection of given row and column number.

Syntax:-  Index(Range, Row_Number, Column_Number)

Match:- Returns the relative position of a value in an range that matches with given value.

Syntax :- Match(Lookup_value, Lookup_Range, Match_Type(Exact/ False))

Solution

      A               B           C             E         F         
  1      Marks      Subject       ID            ID       Marks
  2       65          Math        A005         A006      ?????
  3       75          Math        A006
  4       74          Math        A003

Now to return marks based on ID, Formula would be =INDEX($B$4:$D$6,MATCH(G4,$D$4:$D$6,0),1).

Regards,
Sunil


#3

Simple hackā€¦If number of columns is not a concern in your worksheet, you can also create a copy of the column on the right and then apply VLOOKUP()

Otherwise, the method provided by Sunil works


#4

Hi Guys,

Can somebody help me in doing a lookup from left side of the key in Excel? Please refer to images below for reference -


Regards,
Sumeet


#5

@sumeetbedekar65

The answer from Sunil already does this

Kunal