# How can we look-up values from left in 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