improving multiple match formula

[Hlatigo]

I have the below formula that takes two rows and matches 5 data points. at the point if all five match then I get an "OK". if they dont I get a #N/A. I was hoping to ultimately get something that will tell me whcih cells dont match without having to use 5 or 6 nested IF statements. If this can be done in VBA easier that would be great as well.

thanks!




=IF(AND(MATCH(C32,M32),MATCH(D32,N32),MATCH(E32,O32),MATCH(F32,P32),MATCH(G32,Q32)),"OK","Please Review")

 

[Andrew Poulsom]

To avoid #N/A use EXACT instead of MATCH.

 

[Hlatigo]

Thanks Andrew!

So I have changed to the below but i dont think it will work completely if there are mutiple non-exacts. Is there a way around this?

=IF(EXACT(C18,M18),IF(EXACT(D18,N18),IF(EXACT(E18,O18),IF(EXACT(F18,P18),IF(EXACT(G18,Q18),"OK","Review Offer Price"),"Review OrderQty"),"Product"),"Review Port"),"Review Vessel")

 

[Andrew Poulsom]

Your formula worked when I tried it, with this order of cells:

D Vessel
E Port
F Product
G OrderQty
H Offer Price

It returns the first mismatch.

 

[Hlatigo]

Yes sir,

It works for me doing that as well. WHat if I have two mismatches. I was also thinking of doing conditional formatting but that seems to be too much work for about 200 rows...im too lazy hehehe

 

[Jonmo1]

What would you like to happen if there are multiple Non-Matches ??

I came up with this array formula that will return the FIRST non-match.

I've assumed you have headers (C1:G1) corresponding to the Text strings
"Offer Price","OrderQty","Product","Port","Vessel"

This formula is an array formula, requiring CTRL + SHIFT + ENTER

="Review " & INDEX(C1:G1,MATCH(FALSE,(C18:G18=M18:Q18),0))

This returns #N/A if they are all matches, but can be adjusted to return OK instead

=IF(ISNA(MATCH(FALSE,(C18:G18=M18:Q18),0)),"OK","Review " & INDEX(C1:G1,MATCH(FALSE,(C18:G18=M18:Q18),0)))

 

[Hlatigo]

HI jonmo1

The current formula using nested if...(although i prefer not using them) returns first nonmatch as well. I was hoping to get all non-matches to show up.

Yes, row 17 is a header with the Offer Price","OrderQty","Product","Port","Vessel" as titles.

Do you think its too much to point out all non matches for the user?

 

[Jonmo1]

So you would like the result to be

"Review Offer Price, Product, Vessel"

Yes I think that'll be overdoing it a bit...

Just let it identify the first.
User can correct the first
then the formula will now show the 2nd
user can then correct the 2nd..

 

[Hlatigo]

ahhh...good idea! thanks