MrSamExcel
Board Regular
- Joined
- Apr 6, 2016
- Messages
- 51
- Office Version
- 365
- 2021
- Platform
- Windows
Is there a non-array Index Match formula that references field names instead of specific columns and allows for multiple search criteria? Here is my original post that solves the "reference field names" function:
Index Match / lookup formula using column names
Can I expand on this approach to account for 2 criteria? In the orig example, the formula returns DESCRIPTION from 'source' tab if it finds a match for A2 from 'formula' in the FUNDCODE field of 'source'. What about a formula in B2 that returns DESCRIPTION if FUNDCODE = A2 and FEEDID <> A2? I found a non-array INDEX/MATCH with multiple criteria formula using specified columns, but could not adapt it to reference field names like the original post. Thanks.
Excel 2013 64 bit
Index Match / lookup formula using column names
Can I expand on this approach to account for 2 criteria? In the orig example, the formula returns DESCRIPTION from 'source' tab if it finds a match for A2 from 'formula' in the FUNDCODE field of 'source'. What about a formula in B2 that returns DESCRIPTION if FUNDCODE = A2 and FEEDID <> A2? I found a non-array INDEX/MATCH with multiple criteria formula using specified columns, but could not adapt it to reference field names like the original post. Thanks.
Excel 2013 64 bit
A | B | |
---|---|---|
1 | FUND_CODE | FUND_NAME |
2 | AA | =INDEX(source!$A$1:$F$5,MATCH(A2,INDEX(source!$A$1:$F$5,,MATCH("FUNDCODE",source!$A$1:$F$1,0)),0),MATCH("DESCRIPTION",source!$A$1:$F$1,0)) |
3 | GA | |
4 | CO | |
5 | CT |
Sheet: formula |
A | B | C | D | E | F | |
---|---|---|---|---|---|---|
1 | FEEDNAME | FEEDID | FEEDDATE | FUNDCODE | DESCRIPTION | CLASS |
2 | Mutual Funds Setup | MFDP0802 | 12/31/2016 | AA | American | A |
3 | Mutual Funds Setup | MFDP0802 | 12/31/2016 | CO | Colorado | C2 |
4 | Mutual Funds Setup | MFDP0802 | 12/31/2016 | CT | Connecticut | I |
5 | Mutual Funds Setup | MFDP0802 | 12/31/2016 | GA | Georgia | C |
Sheet: source |