How to 'vlookup' on multiple criteria

[rob2]

I have 2 sheets:

SheetA:

""	Volg	Uitdeel	Nr
1	13-02-17	024
2	11-09-17	276
3	M	305
4	24-03-14	396

SheetB:

Seq	Datum	Locatie	muzieknr
45	9-09-2017	uit	526
46	9-09-2017	uit	521
47	9-09-2017	uit	522
48	9-09-2017	uit	495

52	24-09-2017	Icht	523
53	24-09-2017	Icht	396
				
54	14-10-2017	CD	526
55	14-10-2017	CD	396
56	14-10-2017	CD	477
57	14-10-2017	CD	520
				
58	26-11-2017	GK	530
59	26-11-2017	GK	498

What I want is the first cell in sheetA colored when SheetA.D (Nr) does not exist in SheetB.D (OK, this is a simple vlookup()), but I want it with additional criteria that the date in the matching row in sheetB.B > today.

How do I do this?

[karolus]

If a Nr doesnt exist → no matching row

[rob2]

Wel, if the number does exist (criteria 1), then I still only want the result to be true if also the date in the second sheet is newer then today (criteria 2).

[jimk]

For a formula similar to VLOOKUP that uses multiple criteria, combine INDEX and MATCH.

Create a cell style called “Red” and enter this long formula into A2 on sheetA. Then drag to fill down to A5.

=ROW()-1+STYLE(IF(ISNA(MATCH(D:D,sheetB.D:D,0)),"Red",IF(INDEX(sheetB.B:B,MATCH(D:D,sheetB.D:D,0),0)>TODAY(),"Default","Red")))

The result is that numbers 1 through 3 are colored red, and only number 4 is colored black.

Explanation:

• ROW() - gives 1, 2, 3, 4 et cetera. That’s the number that will be shown in the cell. The values could be hard coded instead, but this formula is easier to drag and fill.
• + STYLE() means that the rest of the formula will not affect what is shown in the cell, but instead, determines the style.
• IFNA - for error checking, because for numbers 1, 2, and 3 there are no matches.
• MATCH - looks up the corresponding row in sheet B, column D.
• INDEX - gets the value of the cell in sheet B, column D.

• TODAY() - compares the date.

• “Default”, “Red” - use default style if true, and red style if false.

[rob2]

This works to, thanks a lot for figuring this out for me.
Learning every day!

[jimk]

@rob: Glad to hear it works. If this answers the question, then please mark it as correct.

[Lupp]

I did not understand the “multiple criteria”. I only can see one criterion.

You may study this attached example. It should also develop a bit the answer posted as a comment by @karolus.