CODEWITHSUNDEEP
excelexcel-formula
Labbiqa
Labbiqa

Reputation: 157

A formula to match data with date range

Click here for an example of what I am trying to do

Given a range of dates and data including names, two options S/BS, and dates.

I've made a table with names in the first row.

I need help to write an Excel formula that will do the following: If a date in the data matches a date in the date range, I need a formula that will place the option S or BS at the correct place under the correct name.

Say we have the date 04/01/2015 for Maria with the option BS. The formula needs to find the row where 04/01/2015 is placed in the date range, ie. row 5 and place it under Maria, ie. column M. This would write BS in M5

The formula needs to work for different data sets with the same dates, ie. it's possible for two or more people to have the same date (Maria and Iben both have the date 04/01/2015).

I've tried using VLOOKUP and INDEX+MATCH, but can't quite make it work. I'm not too familiar with these functions either which makes it even harder.

Upvotes: 0

Views: 1001

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Use this array formula:

=IFERROR(INDEX($G$2:$G$7,MATCH($A$2:$A$27,IF($F$2:$F$7=M$1,$H$2:$H$7),0)),"")

Note:

There is a specific order to entering this formula:

  1. Highlight M2:M27 with M2 as the active cell.
  2. Enter the formula in the formula bar.
  3. Instead of Enter when exiting edit mode hit Ctrl-Shift-Enter. If done properly Excel will fill the entire highlighted area with the formula and the formula will have {} around them.
  4. Copy/drag the entire highlighted area over two more columns.

enter image description here

Another method: Use this array formula:

=IFERROR(INDEX($G$2:$G$7,MATCH($A2,IF($F$2:$F$7=M$1,$H$2:$H$7),0)),"")

Procedure:

  1. Enter this formula in M2.
  2. On exiting edit mode instead of Enter hit Ctrl-Shift-Enter. If done properly Excel will put {} around the formula.
  3. Copy/Drag down the length of the date data.
  4. Copy Drag all over two more columns.

enter image description here

One more note, In the test data you provided there is an extra space after the name Thomas in N1. This will need to be removed to test this formula properly. The names must match exactly.

Upvotes: 1

Related Questions