The formula demonstrated in cell D13 is a regular formula, most people prefer a regular formula over an array formula if possible, see above picture.
It combines both values you want to look for using the ampersand character.
Then it concatenates the two cell ranges also using the ampersand character, the INDEX function makes it a regular formula.
The MATCH function then returns the relative position of the of the combined values, see picture above.
MATCH(B13&C13,INDEX(B3:B10&C3:C10,),0) returns 6. The value is in the 6th position in the array.
and returns Jennifer in cell D13.
If you don't mind array formulas, the only advantage is that it is somewhat smaller, use this formula:
To enter an array formula press and hold CTRL + SHIFT simultaneously, then press Enter once. Release all keys.
The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
INDEX + MATCH - multiple conditions.xlsx
INDEX and MATCH – multiple criteria and multiple results
INDEX MATCH – multiple results
INDEX MATCH – Case sensitive
Match two criteria and return multiple records
SMALL function – INDEX MATCH
Lookup two index columns using min max values and a date range as criteria
5 easy ways to VLOOKUP and return multiple values
Find latest date based on a condition
Sort a column alphabetically
Find closest value
How to perform a two-dimensional lookup
Calculate average of last 10 data with possible blank cells
Leave a Comment
How to add a formula to your comment:
Remember to convert less than and larger than signs to html character entities before you post your comment.
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
You can contact me through this webpage
Mail (will not be published) (required)
Notify me of followup comments via e-mail
User Defined Functions
Advanced Excel Course
Posts in category