英文:
Lookup if cell contains text from lookup columns return third
问题
I am trying to lookup if a cell contains strings from two columns in a lookup table and return a category (third column of lookup table).
Cell examples:
abc 2021 Gross Profit abcd
ab 2022 Gross Profit ADJ abcde
ab ADJ 2021 Gross Profit abcde
cd 2023 Payroll asdf
dage Sales 2021 bce
2020 Payroll Revision abcdef
Output should be, respectively:
| Cells Column | Output Column |
|---|---|
| abc 2021 Gross Profit abcd | Actual |
| ab 2022 Gross Profit ADJ abcde | Adjustment |
| ab ADJ 2021 Gross Profit abcde | Adjustment |
| cd 2023 Payroll asdf | Payroll |
| dage Sales 2021 bce | Sales |
| 2020 Payroll Revision abcdef | Payroll Revision |
Lookup table example [name: lookup_table]:
| Lookup1 | Lookup2 | Category1 |
|---|---|---|
| Gross Profit | Actual | |
| Gross Profit | ADJ | Adjustment |
| Sales | Sales | |
| COGS | Cost of Goods Sold | |
| Payroll | Payroll | |
| Payroll | Revision | Payroll Adjustment |
I am trying to use the following formula to return the category but it is returning a two-value array and does not manage to retrieve the correct category.
英文:
I am trying to lookup if a cell contains strings from two columns in a lookup table and return a category (third column of lookup table).
Cell examples:<br/>
abc 2021 Gross Profit abcd
ab 2022 Gross Profit ADJ abcde
ab ADJ 2021 Gross Profit abcde
cd 2023 Payroll asdf
dage Sales 2021 bce
2020 Payroll Revision abcdef
Output should be; respectivley: <br/>
| Cells Column | Output Column |
|---|---|
| abc 2021 Gross Profit abcd | Actual |
| ab 2022 Gross Profit ADJ abcde | Adjustment |
| ab ADJ 2021 Gross Profit abcde | Adjustment |
| cd 2023 Payroll asdf | Payroll |
| dage Sales 2021 bce | Sales |
| 2020 Payroll Revision abcdef | Payroll Revision |
<br/>
Lookup table example [name: lookup_table]:</br>
Edit: Actual lookup table has hundreds of rows this is an example subset - the lookup table should remain an excel table so the reference range adjusts automatically when a new record is added to the lookup_table
| Lookup1 | Lookup2 | Category1 |
|---|---|---|
| Gross Profit | Actual | |
| Gross Profit | ADJ | Adjustment |
| Sales | Sales | |
| COGS | Cost of Goods Sold | |
| Payroll | Payroll | |
| Payroll | Revision | Payroll Adjustment |
I am trying to use the following formula to return the category but it is returning a two value array and does not manage to retrieve the correct category:
<br/>
=IFERROR(FILTER(IFERROR(
LET(cell,M3,
lookup1_range,lookup_table[Lookup1],
lookup2_range,lookup_table[Lookup2],
category_range,lookup_table[Category1],
contains_both,IF(AND(ISNUMBER(FIND(lookup1_range,cell)), ISNUMBER(FIND(lookup2_range,cell))), 1, 0),
contains_lookup1,IF(ISNUMBER(FIND(lookup1_range,cell)),1,0),
matches_lookup1,IF(contains_lookup1,MATCH(lookup1_range,lookup1_range,0),""),
matches_both,IF(contains_both,IF(ISNUMBER(FIND(lookup2_range,INDEX(lookup1_range,matches_lookup1))),matches_lookup1,""),""),
matches,IF(matches_both<>"",matches_both,matches_lookup1),
lookup_value,IF(matches_both<>"", INDEX(category_range, matches_both, 1), IF(matches_lookup1<>"", INDEX(category_range, matches_lookup1, 1), "")),
IF(lookup_value<>"",
lookup_value,
IF(matches_lookup1<>"",
IF(ISNUMBER(FIND(lookup2_range,cell)),
INDEX(category_range, matches_lookup1, 1),
INDEX(category_range, matches_lookup1, 1)),
"")
)
),
""
),IFERROR(
LET(cell,M3,
lookup1_range,lookup_table[Lookup1],
lookup2_range,lookup_table[Lookup2],
category_range,lookup_table[Category1],
contains_both,IF(AND(ISNUMBER(FIND(lookup1_range,cell)), ISNUMBER(FIND(lookup2_range,cell))), 1, 0),
contains_lookup1,IF(ISNUMBER(FIND(lookup1_range,cell)),1,0),
matches_lookup1,IF(contains_lookup1,MATCH(lookup1_range,lookup1_range,0),""),
matches_both,IF(contains_both,IF(ISNUMBER(FIND(lookup2_range,INDEX(lookup1_range,matches_lookup1))),matches_lookup1,""),""),
matches,IF(matches_both<>"",matches_both,matches_lookup1),
lookup_value,IF(matches_both<>"", INDEX(category_range, matches_both, 1), IF(matches_lookup1<>"", INDEX(category_range, matches_lookup1, 1), "")),
IF(lookup_value<>"",
lookup_value,
IF(matches_lookup1<>"",
IF(ISNUMBER(FIND(lookup2_range,cell)),
INDEX(category_range, matches_lookup1, 1),
INDEX(category_range, matches_lookup1, 1)),
"")
)
),
""
)<>""),"")
答案1
得分: 1
You may use COUNTIF combined with wildcards to count text matches:
=IF(COUNTIF(A1, "*Gross Profit*")=0, "None", IF(COUNTIF(A1, "*ADJ*")=0, "Actual", "Adjustment"))
UPDATE: OP wants to relate the lookup table and data, so here's another approach using array formulas:
Because the formula is an array, you must introduce it by pressing CTRL+ENTER+SHIFT or it won't work!
=IFERROR(INDEX(LOOKUPTABLE[Category1], SUMPRODUCT(MAX(COUNTIF(A1, "*" & LOOKUPTABLE[Lookup1] & "*") * COUNTIF(A1, "*" & LOOKUPTABLE[Lookup2] & "*") * ROW(LOOKUPTABLE[Category1])))-1), "None")
Anyways, I've uploaded a sample to Gdrive in case you want to check:
Notice there is a -1 because the formula returns the absolute row number. Because data on the lookup table starts at row 2, then you must add -1. If data started at row 5, instead of -1, you would need -4 and so on...
英文:
You may use COUNTIF combined with wildcards to count text matches:
=IF(COUNTIF(A1;"*Gross Profit*")=0;"None";IF(COUNTIF(A1;"*ADJ*")=0;"Actual";"Adjustment"))
UPDATE: OP wants to relationate lookup table and data, so here's another approach using array formulas:
Because the formula is array, you must introduce it pressing <kbd>CTRL</kbd>+<kbd>ENTER</kbd>+<kbd>SHIFT</kbd> or it won't work!
=IFERROR(INDEX(LOOKUPTABLE[Category1],SUMPRODUCT(MAX(COUNTIF(A1,"*" & LOOKUPTABLE[Lookup1] & "*")* COUNTIF(A1,"*" &LOOKUPTABLE[Lookup2]&"*")*ROW(LOOKUPTABLE[Category1])))-1),"None")
Anyways, I've uploaded a sample to Gdrive in case you want to check:
Notice there is a -1 because the formulla returns the absolute row number. Because data on lookuptable starts at row 2, then you must add -1. If data started at row 5, instead of -1 you would need -4 and so on...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论