Forum Discussion
formula to help select the right data set
help with formula to select the correct data set.
below B2 to B5 are input tabs and calculation results in B6 using four different data sets (region 1 to 4). I am able to locate the right match using index based on Section and Age, however I have no formula to select the correct region. as seen below, result is pulling up data from Region 1, where it should be Region 4. what should i add on the formula bar to help locate the correct data set?
on a completely separate topic, apologies in advance as I can't seem to find a way to attached the actual worksheet here anymore
5 Replies
A variant using the reference arrangement of INDEX.
=INDEX((Region_1,Region_2,Region_3,Region_4),RIGHT(B3)*1,MATCH(B5,$B$10:$F$10,0),RIGHT(B4)*1)=INDEX(INDIRECT(VLOOKUP($B$4,$O$3:$P$6,2,FALSE)),MATCH($B$3,$A$11:$A$16,0),MATCH($B$5,$B$10:$F$10,0))You can try this formula along with named ranges for the 4 regions and with a reference table. The named range "Region_1" is B11:F16 and named range "Region_2" is I11:M16 and so on.
- hi there, thanks for the response. I like this idea but have not used named ranges before. How did you get to link the named ranges in column P to the data set?
An easy way to define a named range is shown in the screenshot. In the first step i've selected range I21:M26 with the mouse. Then i've entered "Region_4" in the highlighted field. The named range is referenced by the INDIRECT part within the INDEX formula.
