Question regarding if statement for matches

[marcdenney1]

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

 

[steve the fish]

Id go something like this:

=IF(A1=123,IFERROR(MATCH(A1,Sheet2!A:A,0),"Add to Sheet2"),IF(A1=456,IFERROR(MATCH(A1,Sheet3!A:A,0),"Add to Sheet3"),IF(A1=789,IFERROR(MATCH(A1,Sheet4!A:A,0),"Add to Sheet4"),"No Match Anywhere")))

 

[steve the fish]

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

 

[marcdenney1]

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

 

[mohan.pandey87]

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

 

[JimM]

posted in error

 

[steve the fish]

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]

I think he's looking for "cell contains value" rather than "cell equals value"

 

[steve the fish]

What if A1 is 147 ie contained all of the grouped values?

 

[marcdenney1]

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