willow1985
Well-known Member
- Joined
- Jul 24, 2019
- Messages
- 905
- Office Version
- 365
- Platform
- Windows
I need help with an Index Match formula. I am looking to return whatever is in column C as long as 2 criteria is met:
1. There is an exact match between E2 and Column A
2. Column B contains the word in F1 - (I would prefer a cell reference to F1 rather than typing in the text *Blue*)
Below is an example and an incorrect formula that is not returning the results I am looking for. The desired results are below in yellow
Thank you to anyone who can assist me with this!
1. There is an exact match between E2 and Column A
2. Column B contains the word in F1 - (I would prefer a cell reference to F1 rather than typing in the text *Blue*)
Below is an example and an incorrect formula that is not returning the results I am looking for. The desired results are below in yellow
Book1 | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | Name | Item | Index Helper | Blue | Green | Yellow | Red | ||||
2 | Tom | Happy Blue bird | Y | Tom | N | ||||||
3 | Greg | medium Green plant | Y | Greg | N | ||||||
4 | Will | small yellow butterfly | Y | Will | N | ||||||
5 | Mark | large red berry | Y | Mark | N | ||||||
6 | |||||||||||
7 | Desired Results: | ||||||||||
8 | Blue | Green | Yellow | Red | |||||||
9 | Tom | Y | N | N | N | ||||||
10 | Greg | N | Y | N | N | ||||||
11 | Will | N | N | Y | N | ||||||
12 | Mark | N | N | N | Y | ||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2:F5 | F2 | =IFERROR(INDEX($C$2:$C$5,MATCH(1,("*Blue*"=$B$2:$B$5)*(E2=$A$2:$A$5),FALSE)),"N") |
Thank you to anyone who can assist me with this!