# Formula help

#### Donai

##### Well-known Member
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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

I am using excel 2003, so your formula will not work

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

Thanks matty, this works but i'm confused why you have used Sheet2 twice in your formula?

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

Replies
7
Views
484
Replies
5
Views
460
Replies
3
Views
145
Replies
5
Views
258
Replies
0
Views
167

### Forum statistics

1,203,728
Messages
6,056,997
Members
444,902
Latest member
ExerciseInFutility

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

### Which adblocker are you using?

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

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