Question: Multiple Cell/Sheet Indexing

[synergeticink]

Hello all,

I think I have a good one today

I have two sheets

SHEET 1, SHEET 2

Both sheets share 5 columns that are identical with the same headers at the top.

A, B, C, D, E

Goal:

Formula in Cell E of SHEET 2
Reference Cells (A,B,C,D) in the same row
Check(index) SHEET 1
Find row that contains the same data that is referenced in (A,B,C,D) from SHEET 2
And return with the value Cell E of SHEET 1 in Cell E of SHEET 2.


EXAMPLE:

SHEET 1

Column A Column B Column C Column D Column E
1 DOG CAT MOUSE Tomato Building
2 DOG CAT MOUSE BEAR Cup
3 DOG CAT MOUSE CARROT Bread
4 DOG CAT MOUSE PEAR House


SHEET 2:

Column A Column B Column C Column D Column E
1 DOG CAT MOUSE CARROT FORMULA


Formula will look at SHEET 1, find the row that matches Columns (A,B,C,D) which in this case would be row #3 and return and show "Bread" in Column E of SHEET 2.

I hope that makes sense, I used to doing simple INDEXINGS formulas but never multiple cells as a reference index.

[ChemistB]

Without using helper columns, you'll need to enter the formula as a array

In E2 of Sheet2

=INDEX(Sheet1!$E$2:$E$5, MATCH(A2&B2&C2&D2, Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5&Sheet1!$C$2:$C$5&Sheet1!$D$2:$D$5,0))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[synergeticink]

Awesome, thank you ChemistB! Will give it a shot and report back.

[synergeticink]

Thank you ChemistB! Formula worked perfectly. Provided Add Reputation.