Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

INDEX/MATCH: Left Lookup Formula Excel

| | Information Helpful? Why Not Donate.

INDEX & MATCH Formulas to Look Left in a Table of Excel Data

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 draw-back 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.

Syntax
MATCH(lookup_value,lookup_array,match_type)

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 Add-ins 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 [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

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