
Current Special! Complete Excel
Excel
Training Course
for Excel 97  Excel 2003, only $145.00.
$59.95 Instant
Buy/Download,
30 Day Money Back Guarantee & Free
Excel Help for LIFE!
Got any Excel Questions? Free Excel Help
Left Lookup in Excel
See Also: VLOOKUP  Excel Lookup Assistant  Excel Lookup Functions , How to Find the nth Occurrence
Excel is very rich in Lookup
formulas, with perhaps the
VLOOKUP
being the most popular. However, the drawback with all Excel's Lookup
formulas is that they will only look in the left most column and
return the result from the corresponding cell to the right. There are
times when users need to lookup data in any column of a table and
return the corresponding cell to the left. To do so, we can use the
INDEX & MATCH Formula/Functions
INDEX & MATCH
The INDEX Formula/Function has 2 versions available. We will only be
using the first version here;
1) INDEX Formula/Function. Returns the value of a specified cell or
array of cells within array.
Syntax
INDEX(array,row_num,column_num)
2) INDEX Formula/Function.
Returns
a reference to specified cells within reference.
Syntax
INDEX(reference,row_num,column_num,area_num)
The MATCH Formula/Function Returns the relative position of an item in an array that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.
See Excels help for full details on these 2 Formula/Functions.
Left Lookup
To do a left lookup we can use the INDEX Function/Formula with the MATCH Function/Formula nested in the
Row_num Argument of the INDEX
Function/Formula. Let's say our table of data resides in a table named DataTable and this named range refers to:
$A$1:$D$9 See Image
below;
As you can see, the first example uses the formula: =INDEX(DataTable,MATCH("RKP4",ID,0),1)
and makes use of the Named ranges. The second does exactly the same,
but does not use the Named ranges, i.e.
=INDEX($A$1:$D$9,MATCH("RKP4",$C$1:$C$9,0),1)
In Laymen's terms, the formula is telling Excel to use the range
$A$1:$D$9 as the array Argument, row 4 for the row_num Argument (returned via the
MATCH Formula/Function) and Column 1
of the array $A$1:$D$9. Which returns the data in cell A5
(Mary K).
Returning the Column Number and Row NumberWe can either take this a step further and ensure the
column_num
argument supplied is always correct by nesting another MATCH
Formula/Function into the column_num argument. The formula for this
would be;
=INDEX(DataTable,MATCH("RKP4",ID,0),MATCH("Name",Headings,0))
OR, with no Named Ranges
=INDEX($A$1:$D$9,MATCH("RKP4",$C$1:$C$9,0),MATCH("Names",$A$1:$D$1,0))With both the above examples, we can assure that moving the
Names
Column will not cause our formula to return an incorrect result.
See Also: VLOOKUP  Excel Lookup Assistant  Excel Lookup Functions , How to Find the nth Occurrence
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Addins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages  Trading In Excel  Construction Estimators  Finance Templates & Addins Bundle  CodeVBA  SmartVBA  PrintVBA  Excel Data Manipulation & Analysis  Convert MS Office Applications To......  Analyzer Excel  Downloader Excel
 MSSQL Migration
Toolkit 
Monte Carlo Addin 
Excel
Costing Templates