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 ...
=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.
- Thank you!! It's the weekend here now so I will test this out on Monday! Happy weekend