Lookup formula needed

[Norbert]

Ok, this is a bit tricky to explain but hopefully you can follow this example:

• on sheet "Prod.ticket" there are 5 cells which values have to be compared with
a table on sheet "Warp ID."
cell AJ24 value: 167
cell AQ24 value: Black
cell AJ27 value: 167
cell AQ27 value: Beige
cell DA48 value: 4850

• the table on sheet "Warp ID." has possible values of above cells listedin
columns B-F. When there is a match, the formula on sheet "Prod.ticket" in
cell P53 should bring back the value of column G of that table on sheet
"Warp ID.".

This is what the table looks like:
A B C D E F G
PEF 167 Black 4850 H01
PEF 167 Beige 4850 H02
PEF 167 Grey 4850 H03
PEF 167 Black 167 Beige 4850 H04
PEF 500 Black 4850 H05

I need a formula for sheet "Prod.ticket" P53, which looks up the various values
in above mentioned cells (AJ24, AQ24, AJ27, AQ27, DA48) and compares those values with the table on sheet "Warp ID." and brings back the value of column G.
In above example, the formula in P53 should bring up as result: H04

I hope that somebody can help me with this.
Regards,
Norbert

 

[Norbert]

I found the solution! I had to link to the cells from sheet "Warp ID." though, as the cells on sheet "Prod.ticket" are merged cells and it won't work with them.

Here is the formula (array formula) I'm using now:

=INDEX(G5:G100,MATCH(1,(B5:B100=B2)*(C5:C100=C2)*(D5100=D2)*(E5:E100=E2)*(F5:F100=F2),0))

the formula is to be entered with Ctrl+Shift+Enter