Thread: Change a range to a named selection

All,

I have no problem changing my range to a named array. This is a bit trickier than that; here is my formula:

=(SUMPRODUCT(ISNUMBER(MATCH(ROW('All Games'!$E$157:$E$168),INDEX(LARGE(('All Games'!$E$157:$E$168=C12)*ROW('All Games'!$E$157:$E$168),{2}),0),0))*'All Games'!$F$157:$F$168))

Right now I search E157:E168, index the selection, and select corresponding data in column F. What I would like to do is select all rows from 157-168 via a named array and sub-select the various columns when I need them. I've done this sort of thing with VLOOKUP before but I am not sure how to integrate that approach with my current formula. Ideally I want to do this:

=(SUMPRODUCT(ISNUMBER(MATCH(ROW(named array, column E),INDEX(LARGE(((named array, column E)=C12)*ROW(named array, column E),{2}),0),0))*named array, column F))

A helping hand would be greatly appreciated.

Thanks,
The Dropper

Hard to tell without seeing what you're doing. Maybe

=SUMPRODUCT(ISNUMBER(MATCH(ROW(tbl), INDEX(LARGE( (INDEX(tbl, 0, 5)=C12) * ROW(tbl), 2), 0), 0)) * INDEX(tbl, 0, 6))