How to Assign an array Name to a number value to use in HLOOKUP

[gordco]

My Values sheet has a column that needs to be populated with values obtained from the Tables sheet (same workbook) that has multiple tables. I named each table as _89MS and _89MNS. My HLOOKUP needs to be able to determine which table (multiple tables in one worksheet) to extract values from. I actually have several tables, but have included 2 for this example. I do other calcs that end with a value that indicates what table to use. It's a static table of values. For instance a value of 339 aligns with table 89MS, and 364 aligns with 89MNS. So if my calculation renders 339, then I need to use table 89MS. My conundrum is how do I assign the value of 339 to the named array of _89MS, and hence use that named array in my HLOOKUP formula.

I attached a simple Excel as an example. Note, that I am using Excel 2016. Will need to update my profile. Thanks

[oeldere]

I re-aranged your 2 tables in to 1 table and add the values together.

after that an index/match to get the data.

see the attached file.

[Glenn Kennedy]

Or... change your table of static values to reflect the actual names of the tables

_89MS and not 89MS

and then use:

=HLOOKUP($B$2,INDIRECT(VLOOKUP($B$22,$B$16:$C$20,2,FALSE)),A6+1)

in B6, copied down.

[gordco]

I re-aranged your 2 tables in to 1 table and add the values together.

after that an index/match to get the data.

see the attached file.

OELDERE, Thank you very much for the assistance. I used the other respondent's solution which didn't require changes to the tables.
Gordco

[gordco]

Or... change your table of static values to reflect the actual names of the tables

_89MS and not 89MS

and then use:

=HLOOKUP($B$2,INDIRECT(VLOOKUP($B$22,$B$16:$C$20,2,FALSE)),A6+1)

in B6, copied down.

Glenn, your solution worked great. Much appreciated!
Gordco

[oeldere]

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.