4 Columns Comparison
Hi to all!
I need to compare 4 columns like the ones in the image.
If "A+B" contents are identical to "C+D", in the "E" i want to see the correct "REF" number (1° Column).
If those are not identical i want to show N/A or something similar.
http://imageshack.com/a/img921/3108/cKWoNT.png
What solution do you suggest?
Thanks!
Will the numbers ever occur more than once in the column they are in?
If no then the numbers themselves are unique and there's no need to compare columns B and D and you can use this:
in E1
=MATCH(A1,C$1:C$4,0)
and copy down the column
else use this
=MATCH(A1,IF(B1=D$1:D$4,C$1:C$4),0)
Array formula, use Ctrl-Shift-Enter
and copy down the column
Last edited by Special-K; 02-08-2016 at 01:15 PM.
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Yes, there could be duplicate numbers in the columns A and C.Originally Posted by Special-K
Try the second formula above
Note it's an array formula
In my case i have about 20.000 rows.
Is this formula correct for all those rows?
Thanks!
Just extend the ranges with the dollars in to match your rows, ie C$1:C$20000
Ok, perfect! And if i want to show the correct REF number? What do i have to do?
Imagine that the 1° row has 233 like reference, the second row 421, the third 677 etc....
Thanks for the suppport!
Try (re your original file)
=INDEX($A$1:$A$4,MATCH(B1,IF(C1=E$1:E$4,D$1:D$4),0))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks