CODEWITHSUNDEEP
excelexcel-formulalookup
Pavi
Pavi

Reputation: 345

Look up for two values

enter image description here

I Have data set which contains Duration and Code with subject wise material observed. Which formula i have to use to get the value of total for month=june & Code=BHEB. I have tried using two dimension look up formula. 

=INDEX(DataRange, MATCH(Code,Code,0), MATCH(Durations,Duration,0))

it doesn't suit my suitation, i cant change month value to column.

Upvotes: 0

Views: 56

Answers (1)

pnuts
pnuts

Reputation: 59440

With a helper column (may be hidden), say inserted as A in the image, populated with: 

=B1&"|"&C1

and copied down to suit then: 

=INDEX(DataRange, MATCH(Code&"|"&Semester,A:A,0), MATCH(Durations,Duration,0))

may serve where the named ranges are:

DataRange : =Sheet!$C$1:$G$5
Code : =Sheet!$J$1
Semester : =Sheet!$J$2
Durations : =Sheet!$J$3
Duration : =Sheet!$C$1:$G$1

or extended to suit.

SO24333757 example

Upvotes: 1

Related Questions