Index match for multiple criteria

[BryceVBA]

Hello, I'm trying to use the following formula, but A4 and A5 are numbers (9 and 3), so it's just combining them instead of looking twice (93). does anyone know how to format this formula correctly for numbers to be used?

=INDEX(Reference!E1:H253,MATCH(A4&A5,Reference!$G$2:$G$253&Reference!$H$2:$H$253,0))

[xlnitwit]

Hi,

Assuming there will only be one match I would suggest
=LOOKUP(1,1/(A4=Reference!$G$2:$G$253)/(A5=Reference!$H$2:$H$253),Reference!E1:H253)

[BryceVBA]

Hi,

Assuming there will only be one match I would suggest
=LOOKUP(1,1/(A4=Reference!$G$2:$G$253)/(A5=Reference!$H$2:$H$253),Reference!E1:H253)

This formula seems like it would work in theory, however excel isn't matching the 9 to the 9 in the array. the 9 in the array is a reference to the week number with the formula =WEEKNUM(E43) to the corresponding date in the array. The index formula seems to bypass this measure. Any other ideas?

[xlnitwit]

Are you saying that the formula doesn't work, or that you don't think it will?

[BryceVBA]

it doesn't work. The formula errors out looking for 9 in the array. a 9 is in the array, but the cell is coded as =WEEKNUM(E43). Somehow the lookup function doesn't recognize this.

[BryceVBA]

Also there are multiple matches, which is why I was using index