Searching two columns and displaying a third

Closed
Report
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
Hello,
Hello everyone...quick question...I need a formula to search data from two columns and find the single row which matches the specified criteria for both columns...once the match is found... display value found in third column.

Example: Columns A, B, and C contain the data I'm looking for. Find the Row in Column A which matches the Animal (found in Cell F1) and the Color (found in Column D)...when it finds the one row which matches both criteria, return the corresponding name in column C to Column E.

So for the first search, a Red (D1) Cat ($F$1), it searches Column A for a match to F1 and Column B for a match to D1 (there will always be exactly one row which matches btw). Once the row is found (row 4), it will place the value of C4 into E1.

Next, a White (D2) Cat ($F$1), it searches Column A for a match to F1 and Column B for a match to D2. Once the row is found (row 3), it will place the value of C3 into E2.

I really hope I explained this well enough. Thank you in advance for any and all help!

--------------A------------B---------------C----------------D---------------E----------------F------------
1------------Cat---------Brown-------Alex-----------Red-----------David---------Cat
2------------Cat---------Black---------Brian---------White--------Charles
3------------Cat---------White--------Charles------Black--------Brian
4------------Cat---------Red-----------David---------Red-----------David
5------------Bird--------Brown-------Edward------Brown
6------------Bird--------Black--------Frank---------White
7------------Bird--------Red----------George-------Brown
8------------Bird--------White--------Henry--------Black


1 reply

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi ahmetcandan,

I've got a solution for you, although it might not be the best:
Reserve 2 extra columns.
Column G:
Enter '1' in G1 and drag it down.

Column H:
Enter formula in H1:
=SUM(($A$1:$A$8=$F$1)*($B$1:$B$8=D1)*($G$1:$G$8))
Confirm this formula by hitting Ctrl+Shift+Enter as this is an array formula.
Drag the formula down ( make sure the number of rows are correct ).

And finally the formula in column E:
=INDIRECT("C"&H1)
Drag the formula down.

Kind of a workaround. Keep in mind that column G and H can be placed out of sight, be hidden , text be made white or placed on a different sheet. To keep the same look.

Best regards,
Trowa