Lookup formula with multiple tables or range names

**phatmik3** · 12-12-2007, 06:45 PM

Is it possible to create a lookup formula, such as VLOOKUP or INDEX, so that the range name that represents an array or array table can be dependent on the text input into another cell?

For example, I know if I have a table that has inventory item names for the row headings and dates for the column headings with the data being purchase prices, that I can combine INDEX and MATCH functions in a formula so that I can simply type the inventory item name and date into two designated cells within the worksheet and have the purchase price returned.

But if I have multiple tables (all same in type of content) that represent different geographical regions' purchase prices, is there a way to write a formula so that the range name could also be pulled from information typed into a cell?

For example, if I have four tables named east, west, north and south and I name the appropriate ranges of row and columns in each table eastrow, westrow, etc. and eastcolum, westcolumn, etc. can I somehow type those range names into cells for reference within a formula? Every attempt I have made does not work.

I am currently using an additional table that looks up every part for every region for the specific date so that the prices can be pulled into other areas of the spreadsheet. If I could eliminate that additional table it would allow me to eliminate thousands of formulas and precious processing time. I realize I could combine all the tables into one ginormous table, but I would like to avoid doing that since I have many, many tables and different beginning and ending dates.

Thanks for checking out my post and I hope I have made it clear enough to follow without writing out all the specific formulas.

**Paul** · 12-12-2007, 07:44 PM

Hi phatmik,

I think you can do this using the INDIRECT formula.

For example, if you put east in cell A1, eastrow in A2 and eastcolumn in A3, you could do an INDEX/MATCH something like:

Please Login or Register  to view this content.

where B1 and B2 hold the values you're trying to match. You could just as easily substitute various criteria for B1 and B2 and not refer to those cells. With this formula, if you change the values of A1-A3 to, say, west, westrow and westcolumn, you'll now be searching a different table.

Hope that helps..

**phatmik3** · 12-12-2007, 08:18 PM

Originally Posted by pjoaquin

Hi phatmik,

I think you can do this using the INDIRECT formula.

For example, if you put east in cell A1, eastrow in A2 and eastcolumn in A3, you could do an INDEX/MATCH something like:

Please Login or Register  to view this content.

where B1 and B2 hold the values you're trying to match. You could just as easily substitute various criteria for B1 and B2 and not refer to those cells. With this formula, if you change the values of A1-A3 to, say, west, westrow and westcolumn, you'll now be searching a different table.

Hope that helps..

EXACTLY!!!! I just finished an example spreadsheet to help people visualize what I was trying to do and before I had the chance to post it, you nailed it! I am going to include it anyway with this post for anyone who wants to look at it. I used the Vlookup formula instead of Index, but same concept. Output Product Cost C uses your Indirect formula method and is set to produce the same cost as my old method in Product Cost B.

(In my example only two dates 2/12 & 2/19 will produce answers for every region without errors, but that is what I want to happen.)

Thanks again! Perfect solution!