alaskanpilot
Board Regular
- Joined
- Sep 27, 2004
- Messages
- 104
I have a sheet with numerous table arrays, each of which is represented by a named range. Then, I have another variable called "tailNo" which, based on its value, requires a certain table array.
I want to create a formula that takes the known tailNo value, then performs a vlookup using proper table array that belongs with that tailNo value.
It would look something like this:
=VLOOKUP(tailNo, *table array here is based on what value tailNo holds* ,2,FALSE)
So far I came up with this sloppy formula:
=VLOOKUP(tailNo,CHOOSE(MATCH(tailNo,tailList,0),fuelLoading320,fuelLoading321,fuelLoading322,fuelLoading361),2) where four named ranges are listed as choices.
This chooses the correct table array, but as the number of possibilities for "tailNo" grow, so does this formula and its choices. So then I tried using a helper cell: I set up C13 with a formula to display the name of the named range that needs to be used by the VLOOKUP. But when I tried to substitute C13 for the table array, the VLOOKUP thinks C13 is the array, and not the named range in C13.
I hope this makes sense; it's a hard one to describe. Thanks for any help or ideas!
I want to create a formula that takes the known tailNo value, then performs a vlookup using proper table array that belongs with that tailNo value.
It would look something like this:
=VLOOKUP(tailNo, *table array here is based on what value tailNo holds* ,2,FALSE)
So far I came up with this sloppy formula:
=VLOOKUP(tailNo,CHOOSE(MATCH(tailNo,tailList,0),fuelLoading320,fuelLoading321,fuelLoading322,fuelLoading361),2) where four named ranges are listed as choices.
This chooses the correct table array, but as the number of possibilities for "tailNo" grow, so does this formula and its choices. So then I tried using a helper cell: I set up C13 with a formula to display the name of the named range that needs to be used by the VLOOKUP. But when I tried to substitute C13 for the table array, the VLOOKUP thinks C13 is the array, and not the named range in C13.
I hope this makes sense; it's a hard one to describe. Thanks for any help or ideas!
