Formula help

Donai

Well-known Member
Joined
Mar 28, 2009
Messages
543
Hi,

I am after a formula that will look at col A in Sheet1 and try and find a match from either Sheet 2 and 3, as per below screen shots

Excel Workbook
AB
1CodeMATCH
2AU1H
3RTYU
4FGFH
5ERTE
6RYYT
Sheet1


Excel Workbook
AB
1CODEMATCH
2AU1HTEST1
3RTYUTEST2
4RYYTTEST4
5
Sheet2


Excel Workbook
AB
1CODEMATCH
2ERTETEST3
Sheet3
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Maybe:

Code:
=IFERROR(IF(ISNA(MATCH(A2,Sheet2!A$2:A$4,0)),VLOOKUP(A2,Sheet3!A$2:B$2,2,0),VLOOKUP(A2,Sheet2!A$2:B$4,2,0)),"No Match")
This uses IFERROR(), which is only available in Excel 2007 or beyond. It will return "No Match" in case the lookup_value does not exist in either Sheet2 or Sheet3 ("FGFH" in the example posted).

Does this do what you want?

Matty
 
Upvote 0
In that case, try:

Code:
=LOOKUP(REPT("z",255),CHOOSE({1,2},"No Match",IF(ISNA(MATCH(A2,Sheet2!A$2:A$4,0)),VLOOKUP(A2,Sheet3!A$2:B$2,2,0),VLOOKUP(A2,Sheet2!A$2:B$4,2,0))))
Same principle: "No Match" returned in case of no match on either sheet.

Matty
 
Upvote 0
Thanks matty, this works but i'm confused why you have used Sheet2 twice in your formula?
 
Upvote 0
The IF(ISNA(MATCH(...))...) bit is used to establish whether the lookup_value exists on Sheet2. If it doesn't, the VLOOKUP is carried out on Sheet3, else the the VLOOKUP is done on Sheet2.

The whole lot is then wrapped up in the LOOKUP(REPT(...),CHOOSE(...)) part to capture when the lookup_value isn't present on either Sheet2 or Sheet3, in which case "No Match" is returned.

Hope this makes sense.

Matty
 
Upvote 0

Forum statistics

Threads
1,223,308
Messages
6,171,330
Members
452,396
Latest member
ajl_ahmed

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top