Match multiple criteria

[JamiesPhotography]

Hello to you all.

My Spreadsheet is much larger and more complex, but I have trying to simplify this as much as possible to aid in your expertise! So here is my problem...

Imagine in cell A1 I have 01/01/2018, cell B1 I have 02/01/2018, cell C1 I have 03/01/2018 and in D1 I have 02/01/2018

In Cell F6 I have 02/01/2018 (just thrown out of the way)

In cell A2,B2,C2 and D2 is where I want the formula.

Now I want to return "PMI" if any of the cells in A1, B1, C1 or D1 match the value in cell F6, and nothing if none of them match. I worked out a method to do this with one cell with this formula... =IF(MATCH(F6,B1,0),"PMI","") I hope this makes sense to you, if there is a way to do this, I will be able to transfer the method to my spreadsheet.

So I should end up with "PMI" in cell B2 and D2

I have googled and YouTubed with no success and a lot of frustration, and I'm thinking this maybe simpler than I think! I am using Excel 2013.

If I crack this, my boss will be so pleased!!

Thanks in advance.

Jamie

[Special-K]

in A2
=IF(A1=$F6,"PMI","")
and copy across to D2

[JamiesPhotography]

Thanks, I fear I have not explained my problem as well as originally thought. So I have changed my initial post...


Imagine in cell A1 I have 01/01/2018, cell B1 I have 02/01/2018, cell C1 I have 03/01/2018 and in D1 I have 04/01/2018

In Cell F6 I have 02/01/2018 (just thrown out of the way) and cell G6 I have 12/01/18

In Cell F7 is where I want my formula

Now I want to return "PMI" if any of the date in F6 matches any of the dates in A1, B1, C1 or D1, and nothing if none of them match. I worked out a method to do this with one cell with this formula... =IF(MATCH(F6,B1,0),"PMI","") I hope this makes sense to you, if there is a way to do this, I will be able to transfer the method to my spreadsheet.

So I should end up with "PMI" in cell F7 as one of them match

I want to be able to copy the formula across to G7, where it return nothing as the date in G6 does not match.

I have googled and YouTubed with no success and a lot of frustration, and I'm thinking this maybe simpler than I think! I am using Excel 2013.

If I crack this, my boss will be so pleased!!

Thanks in advance.

Jamie

[Sam Capricci]

in the instance you noted in post #3 I would think this would work...
=IFERROR(IF(MATCH(F6,A1:D1,0)=MATCH(F6,A1:D1,0),"PMI",""),"")

[Special-K]

in F7
=IF(COUNTIF($A1:$D1,F6)>0,"PMI","")
and copy across

[JamiesPhotography]

in the instance you noted in post #3 I would think this would work...
=IFERROR(IF(MATCH(F6,A1:D1,0)=MATCH(F6,A1:D1,0),"PMI",""),"")

Thank you for this, the formula didn't work, too many right sided parenthesis with the message too many arguments...but I was able to change it to =IF(MATCH(F6,A1:D1,0),"PMI","") ...can you believe the only thing I was missing was A1:D1, so simple! I think I was trying to over complicate it. Now I know mine returns #N/A when there is not a match and yours was potentially a smarter formula, I'm guessing returning a blank cell when there is no match? But I can hide the #N/A with conditional formatting, and that will work for my needs.

Thank you so much

[Sam Capricci]

well it worked in my representation of your post #3 and I found Special-K's worked as well and was cleaner.
BUT, glad you got yours adjusted as needed.
and thank you for the rep!

[Tommy90]

in the instance you noted in post #3 I would think this would work...
=IFERROR(IF(MATCH(F6,A1:D1,0)=MATCH(F6,A1:D1,0),"PMI",""),"")

It may be a typo here: MATCH(F6,A1:D1,0)=MATCH(F6,A1:D1,0) this will always be true, so If(.... will be always PMI and IFERROR will not be necessary...

and this works???

[Sam Capricci]

It may be a typo here: MATCH(F6,A1:D1,0)=MATCH(F6,A1:D1,0) this will always be true, so If(.... will be always PMI and IFERROR will not be necessary...

and this works???

as the formula was written based off the information in post #3, if I put 1/1/18 in A1, 2/1/18 in B1, 3/1/18 in C1 and 4/1/18 in D1 then put 3/1/18 in F6 and that formula in F7 it returns PMI as requested.
If I put 3/2/18 in F7, without the additional IFERROR it returns #N/A, with the IFERROR it returns a blank.

[Tommy90]

Sambo kid,

Your formula with IF(MATCH(F6,A1:D1,0)=MATCH(F6,A1:D1,0),"PMI","") will be always PMI if MATCH find F6 in that range

Why not =IF(MATCH(F6,A1:D1,0),"PMI","") with only one MATCH

or =IFERROR(IF(MATCH(F6,A1:D1,0),"PMI",""),"") to avoid errors

[Sam Capricci]

Your formula with IF(MATCH(F6,A1:D1,0)=MATCH(F6,A1:D1,0),"PMI","") will be always PMI if MATCH find F6 in that range

No it won't, as in the example in post #9, if I put in 3/2/2018 in F6 which falls in the range between 3/1 and 4/1 it will not return PMI.
Now if you mean that if the value in F6 matches any of the values in A1 through D1 and will return PMI then you are right and that was what the OP wanted unless I misread the request.
And yours without the IFERROR =IF(MATCH(F6,A1:D1,0),"PMI","") also returns #N/A if the date isn't exact.
There are many ways to get solutions in excel so your single match works too, I didn't test mine that way but mine works too. I'm not sure of your point otherwise.

[Tommy90]

MATCH(F6,A1:D1,0)=MATCH(F6,A1:D1,0) is always TRUE (if F6 is in that range A1:D1) or #N/A (if F6 is NOT in that range A1:D1)
but it is non sense to put a=a or b=b

Ok, forget it...