Horizontal and vertical double MATCH function to derive a value via INDEX

[JohnMense]

I have looked at other posts about this and I understand the concept of using MATCH 2x for row and column but when I then use it to try and derive a specific value with INDEX it just isn't working out.

As per attached image I have some raw data of a site location and a project code as per top of image. I then want to derive the 'Product' from the table in the bottom part of the image by:
- MATCH the "Location" in table: MATCH(A$2,$B$13:$D$13,0)
- MATCH the "Code" from the list in the table for the specific site above
- INDEX to derive the Product name

I was thinking the following would work but it comes back as an error:
=INDEX(Sheet1!$A$14:$A$21,MATCH($A2,Sheet1!$B$13:$D$13,0),MATCH(B$2,Sheet1!$B$14:$D$21,0))

Same if I flip the 2 MATCH functions....

If anyone can enlighten me I would appreciate it!

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try

=INDEX(A14:A21,MATCH(B2,INDEX(B14:D21,,MATCH(A2,B13:D13,0)),0))

 

[JohnMense]

@Fluff
1: Formula works great, much appreciated! I see the logic too.
2: Account details updated (365)

Thanks again, this forum has saved my butt on a few occasions.

 

[Fluff]

2: Account details updated (365)

Thanks for that, in which case you could also use

=FILTER(A14:A21,FILTER(B14:D21,B13:D13=A2,"")=B2,"")

 

[JohnMense]

Oddly I can't get that command to work right with no matter how I try to add fixed references ($). When I get to the 3rd line I get a #SPILL! error which also shows on the 4th line but then from 5 onwards it gives the right reference again. I cannot determine why.

I got it down to the following but get the error I mention above: FILTER(A$14:A$21,FILTER(B$14:D$21,B$13:D$13=A2,"")=B2,"")

No matter though as the first solution does the job.

 

[Fluff]

Glad to help & thanks for the feedback.