Index and match on multiple columns

[Perk1961]

I am trying to use the index/match formula to for a new date and time scheduler we are creating where the user selects their name from the drop down menu and the results in the corresponding list shows their desired date and time. The challenge is there are 2 columns to lookup. I can get the date, but I cant' get the time because it resides in a row instead of a column.

Attached is my worksheet. Any assistance would be appreciated.

Thanks in advance.

[Pete_UK]

You could use this formula in H5:

=IF(COUNTIF($C$5:$C$12,F5),$C$4,IF(COUNTIF($D$5:$D$12,F5),$D$4,""))

then copy down.

Hope this helps.

Pete

[hrlngrv]

Try these formulas instead.

G5: =INDEX($B:$B,1/(1/MAX(COUNTIF($F5,$C$5:$D$12)*ROW($C$5:$D$12))))

H5: =INDEX($4:$4,1/(1/MAX(COUNTIF($F5,$C$5:$D$12)*COLUMN($C$5:$D$12))))

Select G5:H5 and fill down into G6:H27. The thing to note is that there's no overlap between dates and times in C5:D12, so the COUNTIF calls above return arrays with one or zero 1s. When there's one 1, multiplying the COUNTIF result by either the ROW or COLUMN result gives the row or column number of the only match in C5:D12. The MAX call picks that up. The 1/(1/x) idiom returns x when x is nonzero, otherwise returns a #DIV/0! error when the COUNTIF call returns zero 1s (all 0s).

You could wrap these formulas inside IFERROR calls, but that'd catch all possible errors. That more often than not a bad idea. You could use conditional formatting to hide specific error values like #DIV/0!. Select G5:H27 with G5 the active cell, set the FORMULA condition as =COUNT(1/(ERROR.TYPE(G5)=2)) and the number format to ;;; .

[hrlngrv]

ignore duplicate posted in error

[Perk1961]

Thank you Pete. This was exactly what I was looking for. I noticed your formula was not an array. Is there a way to modify the formula I used for cell G5 to avoid the array? Just curious.

[Perk1961]

Thank you.

[Pete_UK]

You could use this formula in G5:

=IFERROR(INDEX(B:B,MATCH(F5,C:C,0)),IFERROR(INDEX(B:B,MATCH(F5,D:D,0)),""))

then copy down as required.

Hope this helps.

Pete

[Perk1961]

Nailed it. Thank you!!