cellMatrix.net
Spreadsheet Modeling and Related Topics

About

Formulas, Charts, and Models Created with Microsoft Excel.
More . . .

Statistics

  • Total Entries - 102
  • Current Viewers - 14

Categories

Recent Comments

Syndicate

Validate

Index-Match Formula

Recently I've had to use formulas that perform left-sided lookups at work. The combination of Index and Match in a formula will do so, but I always seem to have a hard time remembering how to build the formula. Recently I put together this color-coded example to help:

image

The formula in cell C8 looks like this:

=INDEX(Range_of_Values_To_Look_Up,MATCH(Look_Up_Value,Range_of_Look_Up_Values,0),1)

or

=INDEX(B2:B6,MATCH(B8,D2:D6,0),1)

Posted on March 20, 2008 | Filed under Formulas | Comments (1) | Permalink

Page 1 of 1 pages


Very nice, but it’s too bad you can’t color-code the different parts of the formula as well, to match the key. Maybe something to suggest to MS for the next release?

Thx,
JP

Posted by JP  on  05/20  at  06:44 PM

Comment Form

Name: (Required)
E-Mail Address: (Optional)
Location: (Optional)
Web Site Address: (Optional)

Remember my personal information?
Notify me of follow-up comments?

Before submitting your comment, please enter the phrase you see below:
View RSS 2.0 - View ATOM | Validate: XHTML - CSS | Privacy Statement
Powered by ExpressionEngine | Hosted by EngineHosting
This site is not affiliated with Microsoft Corporation
Copyright © 2007 - 2009 | cellMatrix.net