SOLVED

Look up multiple matching values

Occasional Contributor

rossshah_0-1660930783008.png

 

I am trying to find a formula for highlighted cells H2 and H3.  My desired result is on "Final Result" table.  Table 1 and Table 2 are on separate sheets.   Thanks.  

5 Replies
best response confirmed by rossshah (Occasional Contributor)
Solution

@rossshah 

I would go for a PQ solution.

 

@Detlef Lewin 

=IFERROR(OR(FILTER($E$2:$E$5,$D$2:$D$5=FILTER($B$2:$B$5,$A$2:$A$5=G2))=H2),FALSE)

 

Comfortable in vlookup and Index match please follow simple formula,
=IF(VLOOKUP($A$2,$A$2:$B$5,2,0)=D2,INDEX(E2:E5,MATCH($B$2,$D$2:$D$5,0)))




@sivakumarrj 

=IF(VLOOKUP($A$2,$A$2:$B$5,2,0)=D2,INDEX(E2:E5,MATCH($B$2,$D$2:$D$5,0)))

That only worked because you forgot to fix the part E2:E5. Otherwise, "Part X" also comes out in line 3, because x and y have the same entry in the final report.

That is the reason why I used filter().

Thank you so much. I never knew about PQ. At first I did not even know what PQ stood for. After watching few videos on You Tube, it did exactly what I wanted to do. Thanks again for the tip.