Wednesday, August 12, 2009

How to lookup at columns on the left

When you use the Lookup or Vlookup function you can find the value of the related cell in any column on the right of the lookup value. If you need to find a column on the left you can of course move the 'Matching' column position to the left .

However, if this is not possible, you can still achieve the same goal.

Let's say you have an array in A1:B30 named 'Target.'
In G1 you have the lookup value to lookup on column A and would like to show the exact value from column B if found.
This would be a simple vlookup: =Vlookup(G1,Target,2,FALSE)

If you want to lookup on B to get the value from A you can use the Index and Match functions:
=INDEX(Target,MATCH(G1,B1:B18,0),1)

Index finds the value of a cell in row n and column m within the array. The column in this case is static and Match provides the position of the matched row in a column array.

No comments:

Post a Comment

Enter your comment here