Ask Your Question
0

how to 'vlookup' on multiple critera

asked 2017-09-16 17:11:35 +0200

-rob- gravatar image

updated 2017-09-16 17:13:33 +0200

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 flag offensive close merge delete

Comments

If a Nr doesnt exist → no matching row

karolus gravatar imagekarolus ( 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).

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

2 Answers

Sort by » oldest newest most voted
0

answered 2017-09-16 17:41:47 +0200

Lupp gravatar image

updated 2017-09-16 17:50:11 +0200

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.

edit flag offensive delete link more
0

answered 2017-09-16 18:03:09 +0200

Jim K gravatar image

updated 2017-09-16 18:24:48 +0200

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.
edit flag offensive delete link more

Comments

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

-rob- gravatar image-rob- ( 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.

Jim K gravatar imageJim K ( 2017-09-16 21:42:18 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-09-16 17:11:35 +0200

Seen: 1,004 times

Last updated: Sep 16 '17