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]

Change these bits:

MATCH(A1

to:

MATCH(E1

 

[marcdenney1]

Change these bits:

MATCH(A1

to:

MATCH(E1

Thanks Steve,

It works if i change A1 to match one of the search criteria but i need it to search for text contained within A1

Thanks

Marc

 

[steve the fish]

So A1 will contain single digits? 1 to 9?

 

[marcdenney1]

So A1 will contain single digits? 1 to 9?

Apologies that was just an example, its actually words

 

[steve the fish]

Try this replacing the numbers with what you are after. Find is case sensitive so be aware. Can use search instead of find if you don't want this

=IF(COUNT(FIND({"1","2","3"},A1)),IFERROR(MATCH(E1,Sheet2!A:A,0),"Add to Sheet2"),IF(COUNT(FIND({"4","5","6"},A1)),IFERROR(MATCH(E1,Sheet3!A:A,0),"Add to Sheet3"),IF(COUNT(FIND({"7","8","9"},A1)),IFERROR(MATCH(E1,Sheet4!A:A,0),"Add to Sheet4"),"No Match")))

 

[marcdenney1]

Try this replacing the numbers with what you are after. Find is case sensitive so be aware. Can use search instead of find if you don't want this

=IF(COUNT(FIND({"1","2","3"},A1)),IFERROR(MATCH(E1,Sheet2!A:A,0),"Add to Sheet2"),IF(COUNT(FIND({"4","5","6"},A1)),IFERROR(MATCH(E1,Sheet3!A:A,0),"Add to Sheet3"),IF(COUNT(FIND({"7","8","9"},A1)),IFERROR(MATCH(E1,Sheet4!A:A,0),"Add to Sheet4"),"No Match")))

That's perfect, thanks so much for your help