VLOOKUP multiple cells

Hi -

I'm not entirely clear on what you're wanting for a result. Can you post an example with 3 or 4 sheets and a 5th sheet where you would show the output you want?

OK - I have the Progress Workbook you uploaded, but I still need a little more detail.

What cell on the INFO sheet do you want something to show that would come from the PRICING and PRODUCTS sheet. And, what cell on the PRICING and PRODUCTS sheet would this information come from? For example, tell me something like "On the INFO sheet, cell L56, I want the total amount shown on PRICING sheet, cell S169 to show up and it should read 3850.00." Or something to that effect.

OK - I think I see what you are wanting. We will use MATCH and INDEX functions to have Excel look up the information from the Products Sheet based on whatever product is selected in the Pricing sheet. So in Cell L7 we have the following formula:

=INDEX(Products!$F$2:$F$444,MATCH($E$6,Products!$B$2:$B$444,0)-(13-ROW(1:1)))

MATCH looks in Column B of the Product sheet and returns the row number of the end of the product data (which is how you have your data organized). Then INDEX Looks in Column F to return the specification category (e.g., Length) for the product. Since the data is organized in reverse order on the PRODUCT sheet versus how you want it shown on the INFO sheet, I have to use the number 13 (which is how many lines of data each solar panel has) and then I want to subtract from there to go to the top of the list which is why I have the ROW function in there. When you copy the formula down, the ROW function automatically increments. The next column on the INFO Sheet has almost the exact same formula, but this time INDEX looks in the next column to the right on the PRODUCT sheet to get the actual specification (e.g., 990). That formula looks like this:

=INDEX(Products!$G$2:$G$444,MATCH($E$6,Products!$B$2:$B$444,0)-(13-ROW(1:1)))

So the long and short of it is you should just be able to change the product on the Pricing sheet and the data on the Info sheet should update automatically. I didn't do the whole thing, just columns L, M, and N on the INFO sheet. You would do the same thing for the inverters. The first cell (L22) would look like this:

=INDEX(Products!$F$2:$F$444,MATCH($E$31,Products!$B$2:$B$444,0)-(10-ROW(1:1))) and copy down.

Note the inverters only have 10 lines of data so we are subtracting 10 rows from the row of the product name to go to the top of the list of data.

I have attached a copy of your spreadsheet. As I said I haven't completed all of it so you have a bit of work to do to finish it. Also, some of your formatting will need to be fixed (bold type, column widths, etc.)

Hope this helps. Please post back here if you have any trouble.

You're welcome Rob. I don't know about lovely, but I am a man.

Be sure to mark this thread as SOLVED if you are satisfied with the result.

Hi Rob -

Getting the pictures to change will take a little bit of work. But not too terrible. I would add another sheet to your workbook called "Pictures". Then, insert each picture you want to use on that sheet, each within 1 cell. Size the pictures to whatever size you want it to be, and resize each cell to match the picture size. Then you are going to do something that is pretty cool (and I just learned this today while researching your problem). You are going to create some names in your workbook for each picture and then you are going to use the INDIRECT command to switch the pictures on your Info form based on what is picked in your Pricing sheet. The following is a link to some instructions on how to do it. It's a teensy bit confusing at first, but I did get it to work - you just need to follow the instructions and read them carefully so you understand each step.

http://excel.tips.net/T003128_Displa..._a_Result.html

If you have trouble, just message me and I can help.

Good luck!