LOOKUP or other functions to return TRUE/FALSE/NA when comparing with multiple columns

[atisyam]

**This exact thread was originally posted previously. But when I checked the forums and my profile, I couldn't find it. So I am reposting this again.


Hello. Hope that I could get some help. Sorry for the vague title.


Based on the example below, I would like column K to return TRUE, FALSE or NA. Basically:


K2 and K3 are NA. This is because G2 is '-' and G3 is a blank, where there is no previous Reg No. and Location to compare to.


K4 is FALSE. This is because the value in G4 is 10 (see H2 & G4), which is correct, with respect to the Reg No. (see B2 & B4) and Location (see I2 & I4)


K5 is TRUE. This is because the value in G4 is 10 (see H3 & G5), which is incorrect, with respect to the Reg No. (see B3 & B5) and Location (see I3 & I5)


[TABLE="class: grid, width: 575"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Reg No.
[/TD]
[TD]Date
[/TD]
[TD]Box No.
[/TD]
[TD]Box Type
[/TD]
[TD]Box Model
[/TD]
[TD]Old S/N
[/TD]
[TD]New S/N
[/TD]
[TD]Location
[/TD]
[TD]Reason
[/TD]
[TD]Different vehicle
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]A123
[/TD]
[TD]1/1/13
[/TD]
[TD]-
[/TD]
[TD]Square
[/TD]
[TD]Pink
[/TD]
[TD]-
[/TD]
[TD]10
[/TD]
[TD]Back Cover
[/TD]
[TD]New
[/TD]
[TD]NA
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]A456
[/TD]
[TD]1/1/13
[/TD]
[TD]

[/TD]
[TD]Square
[/TD]
[TD]Pink
[/TD]
[TD]

[/TD]
[TD]11
[/TD]
[TD]Back Cover
[/TD]
[TD]New
[/TD]
[TD]NA
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]A123
[/TD]
[TD]1/2/13
[/TD]
[TD]10
[/TD]
[TD]Square
[/TD]
[TD]Pink
[/TD]
[TD]10
[/TD]
[TD]15
[/TD]
[TD]Back Cover
[/TD]
[TD]Maintenance
[/TD]
[TD]FALSE
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]A456
[/TD]
[TD]1/2/13
[/TD]
[TD]10
[/TD]
[TD]Square
[/TD]
[TD]Pink
[/TD]
[TD]10
[/TD]
[TD]16
[/TD]
[TD]Back Cover
[/TD]
[TD]Maintenance
[/TD]
[TD]TRUE
[/TD]
[/TR]
</tbody>[/TABLE]


Appreciate anyone who can help. Thanks!

 

[steve the fish]

This potentially could work as im not sure what happens if there are more than one duplicate. Place in K2:

=IF(COUNTIFS($B$1:B1,B2,$I$1:I1,I2)=0,"NA",IF(COUNTIFS($B$1:B1,B2,$H$1:H1,G2,$I$1:I1,I2)=0,TRUE,FALSE))

It will be pretty slow if there is lots of data though.