Forum Discussion
Match data to a range if a match is found perform lookup, if no match found repeat.
I have the formula below works nicely but Ideally if a match is not found I then want to repeat the process checking for a match in a different worksheet within the same workbook if a match is found then I want to perform a different lookup in a different worksheet.
=IF(ISERROR(MATCH(Estimate!C4,'204 Customers'!A:A,0)),"No Match",INDEX('K:\204 Central Accounts\Bib\[Bib Ops Center.xlsm]FPS_Parts'!$D$3:$D$90000,MATCH(Estimate!B50,'K:\204 Central Accounts\Bib\[Bib Ops Center.xlsm]FPS_Parts'!$B$3:$B$90000,0)))Example below whilst it does not work I think it explains what I want to do! excel accepts the formula but I get "#VALUE!" as a result not sure where am going wrong but sure its something simple!!
=IF(ISERROR(MATCH(Estimate!C4,'204 Customers'!A:A,0)),"No Match",INDEX('K:\204 Central Accounts\Bib\[Bib Ops Center.xlsm]FPS_Parts'!$D$3:$D$90000,MATCH(Estimate!B50,'K:\204 Central Accounts\Bib\[Bib Ops Center.xlsm]FPS_Parts'!$B$3:$B$90000,0))),IF(ISERROR(MATCH(Estimate!C4,'772 Customers'!A:A,0)),INDEX('K:\ESL 772\Info Centre\[772 Ops Center.xlsm]Parts'!$F$5:$F$90000,MATCH(Estimate!B51,'K:\ESL 772\Info Centre\[772 Ops Center.xlsm]Parts'!$B$5:$B$90000,0),"No Match"))Hope someone can help me close this one down.
Mark, it could be like this
=IF(ISERROR(MATCH(Estimate!C4,'204 Customers'!A:A,0)), IF(ISERROR(MATCH(Estimate!C4,'772 Customers'!A:A,0)),"No Match", IFERROR( INDEX('K:\ESL 772\Info Centre\[772 Ops Center.xlsm]Parts'!$F$5:$F$90000, MATCH(Estimate!B51,'K:\ESL 772\Info Centre\[772 Ops Center.xlsm]Parts'!$B$5:$B$90000,0)), "Not Found 772") ), IFERROR(INDEX('K:\204 Central Accounts\Bib\[Bib Ops Center.xlsm]FPS_Parts'!$D$3:$D$90000, MATCH(Estimate!B50,'K:\204 Central Accounts\Bib\[Bib Ops Center.xlsm]FPS_Parts'!$B$3:$B$90000,0)), "Not Found 204") )
7 Replies
Hi Mark,
Please check parenthesis, your formula is two sequential IF like
=IF(1,2,3),IF(0,101,102)
which returns #VALUE error. If evaluate it returns 2,102 and error on next step.
With so long formulas better to use some formatting, even in Notepad. You current formula is
=IF(ISERROR(MATCH(Estimate!C4,'204 Customers'!A:A,0)), "No Match", INDEX('K:\204 Central Accounts\Bib\[Bib Ops Center.xlsm]FPS_Parts'!$D$3:$D$90000, MATCH(Estimate!B50,'K:\204 Central Accounts\Bib\[Bib Ops Center.xlsm]FPS_Parts'!$B$3:$B$90000,0) ) ), IF(ISERROR(MATCH(Estimate!C4,'772 Customers'!A:A,0)), INDEX('K:\ESL 772\Info Centre\[772 Ops Center.xlsm]Parts'!$F$5:$F$90000, MATCH(Estimate!B51,'K:\ESL 772\Info Centre\[772 Ops Center.xlsm]Parts'!$B$5:$B$90000,0), "No Match") )Thanks for response Sergei appreciate the response however I suspect if I understood your response fully I would not have required assistance :0/
The formula I posted was simply to try and better explain what I was trying to achieve! Your explanation whilst am sure accurate doesn't really help someone with my level of knowledge (layman)
The formula am using below works fine I just need to know how to repeat with different locations if possible. I can shorten later using named ranges etc.
=IF(ISERROR(MATCH(Estimate!C4,'204 Customers'!A:A,0)),"No Match",INDEX('K:\204 Central Accounts\Bibbys\[Bibbys Ops Center.xlsm]FPS_Parts'!$D$3:$D$90000,MATCH(Estimate!B50,'K:\204 Central Accounts\Bibbys\[Bibbys Ops Center.xlsm]FPS_Parts'!$B$3:$B$90000,0)))Thanks again
Mark, let me clarify the logic, I didn't catch what do you mean under "repeat the lookup"
If first match is
MATCH(Estimate!C4,'204 Customers'!A:A,0)
and second match is
MATCH(Estimate!C4,'772 Customers'!A:A,0)
When
if <first match> then if <second match> then <index/match on second workbook> else "no match" else <index/match on first workbook>Like this?
