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
 

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
Joined
Oct 20, 2009
Messages
8,717
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,717
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,717
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,425
Office Version
  1. 2019
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,717
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
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top