PDA

View Full Version : search and compare



kroz
11-12-2010, 05:02 AM
It looks like i'm better with Macros then with normal formulas. Here's what i'm trying to do and where i am stuck at:

I have a let's say 10 columns table and I'm trying to see if the right part is the same as the left part of my table. To do this i created another column in the left part like so:

A B C D have all values/formulas. Column E has this: "=B&"/"C&"/"D"

I do the same to my right side table:

F G H I have all values/formulas. Column J has this: "=F&"/"G&"/"H"

I then apply a

=IF(NOT(ISNA(VLOOKUP(J10,A:E,5,0))),IF(EXACT(VLOOKUP(J10,A:E,5,0),J10),"Match found","Match not found"),"Value not found")


It wasn't really my idea, i enherited. I want to improve the formula to eliminate the columns E and J.
To do this i need a VLookup that returns 2&3&4 instead of only one value.

Is there another way of comparing that many cells?

kroz
11-12-2010, 06:40 AM
si got it :)
It's quite big, it's using lots of formulas but it's perfect. And I'm not even using vlookup anymore!


=IF($J17<>"",IF(ISERROR(MATCH($J17,$A:$A,0)),"Property not found",IF(INDEX($A:$E,MATCH($J17,$A:$A,0),2)=$K17,IF(INDEX($A:$E,MATCH($J17,$A:$A ,0),3)=$L17,
IF(INDEX($A:$E,MATCH($J17,$A:$A,0),4)=$M17,"Ok","MID Mismatch"),
IF(INDEX($A:$E,MATCH($J17,$A:$A,0),4)=$M17,"Epaisseur Mismatch","Epaisseur Mismatch/MID Mismatch")),
IF(INDEX($A:$E,MATCH($J17,$A:$A,0),3)=$L17,IF(INDEX($A:$E,MATCH($J17,$A:$A, 0),4)=$M17,"Mat Mismatch","Mat Mismatch/MID Mismatch"),
IF(INDEX($A:$E,MATCH($J17,$A:$A,0),4)=$M17,"Ep Mismatch/Mat Mismatch","Ep Mismatch/Mat Mismatch/MID Mismatch")))),"")