# 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

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

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

Replies
1
Views
54
Replies
3
Views
140
Replies
5
Views
52
Replies
16
Views
98
Replies
8
Views
96

1,109,424
Messages
5,528,682
Members
409,830
Latest member
KT50

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...