Forum Discussion

Brass Contributor

Jul 12, 2018

Solved

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 ...

Formulas & Functions

Requests

SergeiBaklan

Jul 14, 2018

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")
)

SergeiBaklan

Diamond Contributor

Jul 14, 2018

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")
   )

mark ainscough

Brass Contributor

Jul 14, 2018

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

SergeiBaklan

Diamond Contributor

Jul 14, 2018

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?

mark ainscough

Brass Contributor

Jul 14, 2018

MATCH(Estimate!C4,'204 Customers'!A:A,0)

If match found perform following lookup

INDEX('K:\204 Central Accounts\Bibbys\[Bibbys 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 no Match found above then

and second match is

MATCH(Estimate!C4,'772 Customers'!A:A,0)

If Match found perform following lookup

INDEX('K:\204 Central Accounts\pipa\[pipa 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)))

So I need to perform a match to data on another worksheet if a match is found a lookup is performed on another workbook. If no match is found then a check is made on another worksheet for a match and if a match is found on this worksheet another lookup is then performed on a different workbook. Hope this clarify s

SergeiBaklan

Diamond Contributor

Jul 14, 2018

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")
)