Searching two columns and displaying a third

Closed
ahmetcandan - Jul 14, 2018 at 03:50 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jul 16, 2018 at 12:18 PM
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 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Updated on Jul 19, 2018 at 11:26 AM
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