CODEWITHSUNDEEP
excel-formula
K.W.
K.W.

Reputation: 141

Excel `Vlookup` AND `IF` functions

These are my table:

SheetA

      A               B
1    UserID         Time/Date
2    1001           2014-7-1 
3    1002           2014-7-1
4    1001           2014-7-2
5    1001           2014-7-3
6    1002           2014-7-4
          ...etc...

SheetB

       A         B   
1    UserID:    1002
2    2014-7-1    Y
3    2014-7-2    N
4    2014-7-3    N
5    2014-7-4    Y
         ...etc...

I want to check if the UserID (entered manually, B1) in the specific date is absent or not. (with formula, B2:B5)

I used =vlookup("B1",'sheetA$'!$A:$B,2,FALSE) but it only returns the date. column A of SheetB is predefined by typing.

How can I make the formula to check USERID and DATE and return Yes/No

Or any better way for such comparison?

Upvotes: 0

Views: 114

Answers (1)

Nat Aes
Nat Aes

Reputation: 927

I think the best way to solve this is to add a 3rd column that concatenates the User ID and Date/Time. So I would add column C in Sheet A with the following formula:

=CONCATENATE(A2,B2)

This creates a unique identifier of sorts, which you can then easily use in a VLOOKUP. So in Sheet B, I would then use:

=IF(ISERROR(VLOOKUP(CONCATENATE($B$1,A2),SheetA!$C:$C,1,FALSE)),"N","Y")

Upvotes: 1

Related Questions