SOLVED

# Look up multiple matching values

Copper Contributor

# Look up multiple matching values

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.

8 Replies
best response confirmed by rossshah (Copper Contributor)
Solution

# Re: Look up multiple matching values

I would go for a PQ solution.

# Re: Look up multiple matching values

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

# Re: Look up multiple matching values

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

# Re: Look up multiple matching values

=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().

# Re: Look up multiple matching values

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.

# Re: Look up multiple matching values

Hi Expert guys,

I need same help with the below data.

1st table contains the below data.

2nd table(as below data) - 1st column contains the random array arrangement of the 1st table values of 1st column.

Expecting exact corresponding respective values of 2nd table - 1st column results in the 2nd table - 2nd column through =VLOOKUP formula or any other possibility in excel from the values of the 1st table - 2nd column.

1) 1st table.

2) 2nd table

# Re: Look up multiple matching values

That's exactly VLOOKUP as you say or any other lookup function available in your version of Excel.

In general, that's new question, better if you start it as new conversation from here https://techcommunity.microsoft.com/t5/excel/bd-p/ExcelGeneral

# Re: Look up multiple matching values

@SergeiBaklan

Thank you for the suggestion.

I have raised the same.

1 best response

Accepted Solutions
best response confirmed by rossshah (Copper Contributor)
Solution

# Re: Look up multiple matching values

I would go for a PQ solution.