Lookup and retrieve value based on table code (2)

[Lux Aeterna]

This is a follow-up question regarding a previous thread that was successfully handled by @Peter_SSs

I need a formula for cell C21 that will look up the value in B21 on the correct sheet. The correct sheet is specified in P1, and the table for the VLOOKUP function is located in the range N3:O76.

The data should be retrieved from the "D*" sheet, within the range R7:R11. I can make modifications to that range, such as adjusting the data, splitting it into two columns, reversing the order, substituting "to," or anything else necessary. Please note that R7 represents "greater than or equal to" and R11 represents "less than or equal to".

The formula @Peter_SSs used, in case you need it, is

=INDEX(INDIRECT("'"&VLOOKUP(P$2;$N$3:$O$76;2;0)&"'!C$7:C$99");MATCH(B5;--LEFT(INDIRECT("'"&VLOOKUP($P$2;$N$3:$O$76;2;0)&"'!D7:D99");FIND("-";INDIRECT("'"&VLOOKUP($P$2;$N$3:$O$76;2;0)&"'!D7:D99")&"-")-1);1))

A previous version of my file is here. I have made some alterations, but the basic structure remains the same.

 

[Peter_SSs]

You can use " to " separate the values in column R of the lookup sheet but, like the previous thread, those values need to be in ascending order in the column (and also ascending left to right in a 'range cell')
So column R of the lookup sheet would need to be something like this.

Lux Arterna WCST Normative data - backup.xlsm
	R
6	Learning to Learn
7	-10000 to -42.41
8	-42.40 to -27.81
9	-27.80 to -22.21
10	-22.20 to -11.35
11	-11.34
D1

Then your formula in C22 (I assume C21 & B21 in your post above were typos) could be

=INDEX(INDIRECT("'"&VLOOKUP(O$2,$M$3:$N$76,2,0)&"'!N$7:N$11"),MATCH(B22,--LEFT(INDIRECT("'"&VLOOKUP($O$2,$M$3:$N$76,2,0)&"'!r7:r11"),FIND(" to ",INDIRECT("'"&VLOOKUP($O$2,$M$3:$N$76,2,0)&"'!r7:r11")&" to ")-1),1))

 

[Lux Aeterna]

You can use " to " separate the values in column R of the lookup sheet but, like the previous thread, those values need to be in ascending order in the column (and also ascending left to right in a 'range cell')
So column R of the lookup sheet would need to be something like this.

Lux Arterna WCST Normative data - backup.xlsm
	R
6	Learning to Learn
7	-10000 to -42.41
8	-42.40 to -27.81
9	-27.80 to -22.21
10	-22.20 to -11.35
11	-11.34
D1

Then your formula in C22 (I assume C21 & B21 in your post above were typos) could be

=INDEX(INDIRECT("'"&VLOOKUP(O$2,$M$3:$N$76,2,0)&"'!N$7:N$11"),MATCH(B22,--LEFT(INDIRECT("'"&VLOOKUP($O$2,$M$3:$N$76,2,0)&"'!r7:r11"),FIND(" to ",INDIRECT("'"&VLOOKUP($O$2,$M$3:$N$76,2,0)&"'!r7:r11")&" to ")-1),1))

=INDEX(INDIRECT("'"&VLOOKUP($P$2;$N$3:$O$76;2;0)&"'!Y$7:Y$99");MATCH(B14;--LEFT(INDIRECT("'"&VLOOKUP($P$2;$N$3:$O$76;2;0)&"'!Z$7:Z$99");FIND("-";INDIRECT("'"&VLOOKUP($P$2;$N$3:$O$76;2;0)&"'!Z$7:Z$99")&"-")-1);1))

Changed the cell references as necessary, and works perfectly!

 

[Peter_SSs]

Good news. Thanks for letting us know.