PDA

View Full Version : Using Index Match with an unknown range



simora
02-15-2020, 09:51 PM
I have a working formula in Column D of the attached WorkSheet.

=INDEX(Posted_Data!$BK$3:$BK$11,MATCH(B3,Posted_Data!$AT$3:$AT$11,0))

I am using this Index Match function with a hard coded range of A Sheet Named Posted_Data ,
So Posted_Data!$BK$3:$BK$11, However, I would like to make this search range Dynamic based on the value that is listed in My Formula Colum, Row 2, so that the search range changes depending on the current value of the Active Column Row 2 value
Right now, my formula is in Column D, so when I move to Column E, the search range happens to be in !$BL$3:$BL$11,
but that will NOT always be the case, so I need to first search in sheet Posted_Data Range Range BK2:BV2 for the value in ActiveCell Row2
( Finding the Appropriate month )
The formulas are in Sheets(“Report”)
How do I modify my formulas to accommodate this on the Attached WorkSheet ?

p45cal
02-16-2020, 07:17 PM
Cell D3, normally-entered formula:

=INDEX(Posted_Data!$3:$17,MATCH($B3,Posted_Data!$AT$3:$AT$17,0),MATCH(D$2,P osted_Data!$2:$2,0))copied down and across.

simora
02-17-2020, 12:11 AM
p45cal

Thanks. It works as expected.
It was that second Match function that tripped me.

simora
02-19-2020, 03:07 AM
p45cal

Didn't realize that my follow up question was not posted. How did you arrive at
MATCH(D$2,Posted_Data!$2:$2
I tried to replicate it using the actual range Posted_Data!$BK$2:$BV$2,
but it gives me an error.

p45cal
02-19-2020, 04:47 AM
=INDEX(Posted_Data!$BK$3:$BV$17,MATCH($B3,Posted_Data!$AT$3:$AT$17,0),MATCH (D$2,Posted_Data!$BK$2:$BV$2,0))

simora
02-19-2020, 05:20 PM
p45cal:

Again; THANKS !
I did NOT explain my problem. I did not understant the Cell logic notation of
[ Posted_Data!$2:$2,0) ] OR [ Posted_Data!$3:$17 ]
as it relates to the appropriate Worksheet.