# Question regarding if statement for matches

#### marcdenney1

##### New Member
Hi,
I would like a formula to perform a match dependent on the value in the cell.

for example:

if A1 contains "123" match against range A:A in sheet 2
if A1 contains "456" match against range A:A in sheet 3
if A1 contains "789" match against range A:A in sheet 4

i would also like to to return some text depending on the result of the match, which would be different fopr each condition above

e.g. if A1 contains "123" match against range A:A in sheet 2 does not return a match i would like the text"add to sheet2"

is there any easy way to do this?

Thanks

Marc

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### steve the fish

##### Well-known Member
Id go something like this:

#### steve the fish

##### Well-known Member
You didn't say what you wanted to return if match found by the way? It will return row number as is.

#### marcdenney1

##### New Member
Thanks for the response but would the IF not be looking for an exact value rather than a containing value?

#### mohan.pandey87

##### Board Regular

Try this:

=IFERROR(MATCH("Test", INDIRECT(IF(\$A\$1 = 123, "Sheet2", IF(\$A\$1 = 456, "Sheet3", IF(\$A\$1 = 789, "Sheet4", ""))) & "!A:A"),0), "Add To " & IF(\$A\$1 = 123, "Sheet2", IF(\$A\$1 = 456, "Sheet3", IF(\$A\$1 = 789, "Sheet4", "No Sheet"))))

Last edited:

posted in error

#### steve the fish

##### Well-known Member

Thanks for the response but would the IF not be looking for an exact value rather than a containing value?

Not sure what you mean. The formula looks in A1. If it finds 123 it looks in Sheet2 A:A for 123. If it finds it it returns row number if not it returns "Add..". etc etc.

#### Special-K99

##### Well-known Member
I think he's looking for "cell contains value" rather than "cell equals value"

#### steve the fish

##### Well-known Member
What if A1 is 147 ie contained all of the grouped values?

#### marcdenney1

##### New Member
Apologies, you have solved the problem at the top but i omitted one detail

if A1 contains "123" match E1 against range A:A in sheet 2
if A1 contains "456" match E1 against range A:A in sheet 3
if A1 contains "789" match E1 against range A:A in sheet 4

If it returns a match i would like no, if it doesn't I need it to specify which sheet to add it to

thanks for the help everyone

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,651
Messages
5,838,577
Members
430,557
Latest member
MK15

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