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

atisyam

New Member
Joined
Sep 19, 2018
Messages
37
**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)


A
B
C
D
E
F
G
H
I
J
K
1
Reg No.
Date
Box No.
Box Type
Box Model
Old S/N
New S/N
Location
Reason
Different vehicle
2
A123
1/1/13
-
Square
Pink
-
10
Back Cover
New
NA
3
A456
1/1/13


Square
Pink


11
Back Cover
New
NA
4
A123
1/2/13
10
Square
Pink
10
15
Back Cover
Maintenance
FALSE
5
A456
1/2/13
10
Square
Pink
10
16
Back Cover
Maintenance
TRUE

<tbody>
</tbody>


Appreciate anyone who can help. Thanks!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top