# how to 'vlookup' on multiple critera

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?

edit retag close merge delete

If a Nr doesnt exist → no matching row

( 2017-09-16 17:34:56 +0200 )edit

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).

( 2017-09-16 17:57:23 +0200 )edit

Sort by » oldest newest most voted

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.

more

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.
more

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

( 2017-09-16 18:29:49 +0200 )edit

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

( 2017-09-16 21:42:18 +0200 )edit