CODEWITHSUNDEEP
excelexcel-formulalookup
spenser1235yahoocom
spenser1235yahoocom

Reputation: 43

Lookup Start Time and Court Number from a Time Grid

I need a formula to fill the courts and times in the first table by looking the MatchCode up in the Time Grid below. Any match could be scheduled anywhere on the grid. Vlookup would only work with the times on the right and only for the first column, Court Number wouldnt work at all with Hlookup(probably would have worked if I moved court numbers to the bottom of the table but probably only for the first row). IndexMatch and Xlookup didnt seem to work at all.

Preferably using formulas that will work on old versions of Excel too.

enter image description here Time Grid

Upvotes: 0

Views: 82

Answers (2)

Evil Blue Monkey
Evil Blue Monkey

Reputation: 2609

Assuming this situation:

enter image description here

Use this formula in cell B2:

=INDEX($1:$1,AGGREGATE(15,6,COLUMN($F$2:$L$14)/($F$2:$L$14=A2),1))

and this one in cell C2:

=INDEX($E:$E,AGGREGATE(15,6,ROW($F$2:$L$14)/($F$2:$L$14=A2),1))

Upvotes: 2

Spectral Instance
Spectral Instance

Reputation: 2494

The suggestion below will work in all versions of Excel (using array formulae in legacy versions):- screenshot illustrating proposed formulae

The formula in B2 (and copied down) is

=MATCH(1,MMULT(TRANSPOSE(N(A2=$G$2:$M$14)),ROW($1:$13)/ROW($1:$13)),0)

and the formula in C2 (and copied down) is

=(7+MATCH(1,MMULT(N(A2=$G$2:$M$14),ROW($1:$7)/ROW($1:$7)),0))/24

Upvotes: 1

Related Questions