Add additional match values to Index formula

Dustinkli

Board Regular
Joined
Mar 26, 2019
Messages
62
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
I have a formula:

Excel Formula:
=INDEX($D$1:$D$6,MATCH(A1,$C$1:$C6,0))

When used in a spreadsheet like below:

ABCD
Bob
=INDEX($D$1:$D$6,MATCH(A1,$C$1:$C6,0))​
Susan
25​
Susan
=INDEX($D$1:$D$6,MATCH(A2,$C$1:$C7,0))​
Bob
45​
Bill
=INDEX($D$1:$D$6,MATCH(A3,$C$1:$C8,0))​
Jason
78​
Ashley
39​
Bill
19​
Cindy
27​

when put in column B, it will provide the age of the person referenced in Column A.

This works fine, but I have encountered a need to expand the formula where I'd like to reference more than just 1 cell.

So for instance in the code:
Excel Formula:
=INDEX($D$1:$D$6,MATCH(A1,$C$1:$C6,0))
I would like to not just reference A1 but other cells as well. How would I go about adding additional cells to reference in this formula?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
There are only two columns of data, name and age, so it is only possible to search by a single criteria, in this case the name.
You should provide an example table with the additional data.
 
Upvote 0
What results do you expect from the formula?
Using multiple references in the match criteria would serve no purpose and likely create an error if not used correctly.
 
Upvote 0
Here's an example of how I'm trying to use it:

Input NameInput IDInput AgeID ListName ListAge List
6387Mitch25
Output NameOutput IDOutput Age8964Andrew32
=INDEX($E$2:$E$9,MATCH(B2,$D$2:$D9,0))1578John78
7531Lisa54
6698Lorraine69
8234Mike48
4589Joseph23
1257Amanda19

In this example if you put the person's name under "Input Name" it will output the ID below under "Output ID".
If you input the person's ID under "Input ID" it will output the person's Name under "Output Name".

In this case I use the formula like
Excel Formula:
=INDEX($E$2:$E$9,MATCH(B2,$D$2:$D9,0))
where in this example I look at cell B2 (where the ID would be) and It outputs the name.

I would like the formula to look at, in this case, not just B2 but also A2 and C2 so that if you put either the Name, the ID or the age in the Input cells the "Output Name" cell still outputs the name. (In this case assume no ages are the same as this is just for example)
 
Upvote 0
Something like this? You will probably need to array confirm the formulas if you are not using office 365. It would be helpful if you update your profile to show the version that you do use so that we can see what functions you have available to use.
Book1 (version 1).xlsb
ABCDEF
1Input NameInput IDInput AgeID ListName ListAge List
2John786387Mitch25
3Output NameOutput IDOutput Age8964Andrew32
4John1578781578John78
57531Lisa54
66698Lorraine69
78234Mike48
84589Joseph23
91257Amanda19
Sheet4
Cell Formulas
RangeFormula
A4A4=INDEX($E$2:$E$9,MATCH(2,1/((ISNUMBER(MATCH($E$2:$E$9,$A2,0))+ISNUMBER(MATCH($D$2:$D$9,$B2,0))+ISNUMBER(MATCH($F$2:$F$9,$C2,0)))=2)))
B4B4=INDEX($D$2:$D$9,MATCH(2,1/((ISNUMBER(MATCH($E$2:$E$9,$A2,0))+ISNUMBER(MATCH($D$2:$D$9,$B2,0))+ISNUMBER(MATCH($F$2:$F$9,$C2,0)))=2)))
C4C4=INDEX(F$2:$F$9,MATCH(2,1/((ISNUMBER(MATCH($E$2:$E$9,$A2,0))+ISNUMBER(MATCH($D$2:$D$9,$B2,0))+ISNUMBER(MATCH($F$2:$F$9,$C2,0)))=2)))
 
Upvote 0
Something like this? You will probably need to array confirm the formulas if you are not using office 365. It would be helpful if you update your profile to show the version that you do use so that we can see what functions you have available to use.
Book1 (version 1).xlsb
ABCDEF
1Input NameInput IDInput AgeID ListName ListAge List
2John786387Mitch25
3Output NameOutput IDOutput Age8964Andrew32
4John1578781578John78
57531Lisa54
66698Lorraine69
78234Mike48
84589Joseph23
91257Amanda19
Sheet4
Cell Formulas
RangeFormula
A4A4=INDEX($E$2:$E$9,MATCH(2,1/((ISNUMBER(MATCH($E$2:$E$9,$A2,0))+ISNUMBER(MATCH($D$2:$D$9,$B2,0))+ISNUMBER(MATCH($F$2:$F$9,$C2,0)))=2)))
B4B4=INDEX($D$2:$D$9,MATCH(2,1/((ISNUMBER(MATCH($E$2:$E$9,$A2,0))+ISNUMBER(MATCH($D$2:$D$9,$B2,0))+ISNUMBER(MATCH($F$2:$F$9,$C2,0)))=2)))
C4C4=INDEX(F$2:$F$9,MATCH(2,1/((ISNUMBER(MATCH($E$2:$E$9,$A2,0))+ISNUMBER(MATCH($D$2:$D$9,$B2,0))+ISNUMBER(MATCH($F$2:$F$9,$C2,0)))=2)))

When I input the formula as above I get a #DIV/0! error.

I am using Excel 2019 version 1808 build 10358.20061
 
Upvote 0
That error suggests that you didn't array confirm the formula. If it was confirmed correctly then it would return either a valid result or a #N/A error, not a #DIV/0! error.

That said, I've had another look and I'm not sure if my previous suggestion is correct for what you wanted. The first suggestion will cross reference 2 valid entries and find the third (for example find ID based on name and age both matching). This method will take any single entry and fill in the remaining 2 columns.
Book1 (version 1).xlsb
ABCDEF
1Input NameInput IDInput AgeID ListName ListAge List
215786387Mitch25
3Output NameOutput IDOutput Age8964Andrew32
4John1578781578John78
57531Lisa54
66698Lorraine69
78234Mike48
84589Joseph23
91257Amanda19
Sheet4
Cell Formulas
RangeFormula
A4A4=IF(A2<>"",A2,INDEX($E$2:$E$9,IFERROR(MATCH(B2,$D$2:$D$9,0),MATCH(C2,$F$2:$F$9,0))))
B4B4=IF(B2<>"",B2,INDEX($D$2:$D$9,IFERROR(MATCH(A2,$E$2:$E$9,0),MATCH(C2,$F$2:$F$9,0))))
C4C4=IF(C2<>"",C2,INDEX($F$2:$F$9,IFERROR(MATCH(B2,$D$2:$D$9,0),MATCH(A2,$E$2:$E$9,0))))

I've kept the formulas simple so it will not detect if you use 2 inputs that don't match.
 
Upvote 0

Forum statistics

Threads
1,214,070
Messages
6,117,524
Members
448,766
Latest member
00Cameron

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top