Question regarding if statement for matches

marcdenney1

New Member
Joined
Sep 24, 2014
Messages
42
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
 

Some videos you may like

Excel Facts

Links? Where??
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
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 24, 2014
Messages
42
Thanks for the response but would the IF not be looking for an exact value rather than a containing value?
 

mohan.pandey87

Board Regular
Joined
Sep 14, 2012
Messages
146

ADVERTISEMENT

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:

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Nov 7, 2006
Messages
8,350
I think he's looking for "cell contains value" rather than "cell equals value"
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
What if A1 is 147 ie contained all of the grouped values?
 

marcdenney1

New Member
Joined
Sep 24, 2014
Messages
42
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
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top