How to select a different range depending on a drop down box

[RaePatterson]

Hello everyone,
I'm new to the forum and would be grateful of some help.

I have many tables with different values. Each table is allocated to a different person. I have managed to workout how to lookup using index and match function formula.
What I'm trying to find out, is it possible to do a vlookup of the names to change the range of the index match?
The filter is in D2, the answer in H2.
I have searched through the forum but I've obviously not got my terminology correct to find what I want.

Thanks

[FlameRetired]

Hello everyone,

Each table is allocated to a different person. .......................
What I'm trying to find out, is it possible to do a vlookup of the names to change the range of the index match?
The filter is in D2, the answer in H2.

Thanks

1.) What answer do you want to come up in H2?
2.) I am not seeing where the value "Peter" in D2 is part of the formula conditions.

[ChemistB]

You can use OFFSET to focus on each range assuming all tables have the same format

=INDEX(OFFSET($A$2,MATCH(D2,$A$2:$A$50,0)-1,3,3,3),MATCH(E2,OFFSET($A$2, MATCH(D2,$A$2:$A$50,0)-1,1,3,1)),MATCH(F2, OFFSET($A$2, MATCH(D2, $A$2:$A$50,0)-3,3,1,3)))

[ChemistB]

An easier way, since all your tables have the same scales for x and y coordinates...

=INDEX(OFFSET(Data,MATCH(D2,$A$9:$A$50,0)-1,0),MATCH(E2,$B$9:$B$11),MATCH(F2,$D$7:$F$7))

where Data is a defined name for D9:F11

[RaePatterson]

Thanks for the replies


I was looking to add something like "vlookup($D$2,$A$1:$A$25,4,false" to the rest of the formula in H2, to look up column A:A and offset to the start of the range required.
Reading on the internet, I read that using the offset function uses far too many resources and wondered if there was a better way.

I'm just about to try the offset method to see how it works out and will reply shortly.

Thanks again

[RaePatterson]

Hello ChemistB,

I tried this method but couldn't get it to work.
I'm new to using the offset method and must be doing something wrong.
Would it be possible to apply it to my attached file so that I can see where I'm going wrong.

Thanks

[RaePatterson]

1.) What answer do you want to come up in H2?
2.) I am not seeing where the value "Peter" in D2 is part of the formula conditions.

Thanks FlameRetired

I was looking to add something like "vlookup($D$2,$A$1:$A$25,4,false" to the rest of the formula in H2, to look up column A:A and offset to the start of the range required.

[ChemistB]

Here you go. I changed your Blue spec to 5.53 since 0.53 doesn't even fall on your tables (your LCL being 4.0)

[RaePatterson]

Thanks ChemistB, works a treat