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

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

mark ainscough
Brass Contributor
Jul 14, 2018
Not something like, exactly like!

Thanks Sergei cant tell you how long I ve spent searching and trying different variations without success. Top man.

If you ever need help re building a motorbike engine inbox me, That I can do blindfolded!
- SergeiBaklan
  Diamond Contributor
  Jul 14, 2018
  mark ainscough, glad to help. One issue less in this world. Thank you for the suggestion, motorbikes at all and their engines in particular is another planet for me.