INDEX and MATCH across multiple columns based on dropdown list

**omni72** · 11-08-2012, 01:56 AM

Hi Excel Forum community

I have an Excel 2007 question. The details are as follows:

1. I'm working within one workbook
2. I'm working with three worksheets

A data worksheet (named 'Input')
A calculations/results worksheet (named 'Calculations')
A list worksheet (named 'TradeSize')

Based on the item selected from a list in D1 in the 'Calculations' worksheet, I'm trying to do the following:
1. Search Input!AC1:DW1 for the cell matching D1
2. Populate D3 with the result of the above search (i.e. Input!AC3 or Input!AE3 or Input!AG3 or ...)
3. Populate E3 with D3 divided by D1
4. Populate G3 with D3 divided by the figure in preceding the result of step 2 (i.e. if step 2 produced Input!AE3, then it would be D3/Input!AC3)
4. Populate H3 with D3 divided by the figure in following the result of step 2 (i.e. if step 2 produced Input!AE3, then it would be D3/Input!AG3)

My suspicion is that it will in some way involve INDEX and MATCH. However, I'm not very familiar with either, so don't really know how to get this going. I've searched the web for scenarios similar so that I could apply the method to my situation, but so far that is a no-go.

I've included a sample Excel 2007 spreadsheet in hopes of clarifying the scenario. Within the spreadsheet on the second sheet ('Calculations'), I've added a few comments to further illustrate the situation and my objectives.

Thoughts or suggestions?

Thanks, in advance, for your time and help.

Take care -

omni

p.s. If more details are needed, just let me know; I've tried to be as clear as possible but since I'm the one who knows what I'm trying to do it's going to seem clear to me :D

**FDibbins** · 11-08-2012, 02:20 AM

It would be easier to help if you could put some "manual" answers in, so i can see what comes from where

**Ace_XL** · 11-08-2012, 02:30 AM

See if the attached helps..

**omni72** · 11-08-2012, 02:30 AM

Originally Posted by FDibbins

It would be easier to help if you could put some "manual" answers in, so i can see what comes from where

Will do. I'll do it now and re-upload. Thanks for taking a look!

Edit: Worksheet with manually populated data attached.

**omni72** · 11-08-2012, 02:44 AM

Originally Posted by Ace_XL

See if the attached helps..

Just saw your post after I uploaded the edited version, but so far your solution definitely appears to be on the money! Still poking around a bit to make sure I didn't incorrectly convey anything and mislead your efforts.

Thanks so much for your help!

Assuming it does the trick, I'll have to take some time and dissect the formulas you used to better understand what's going on :D

**omni72** · 11-08-2012, 03:00 AM

Thanks to all who took a look at this, your efforts are greatly appreciated. And, of course, extra loud shout-out to Ace_XL for coming up with such an elegant solution. I'm pretty sure it took Ace less time to answer all my questions than it took me to write the original post :D

Never fear ... I have another scenario that has me stumped and likely find its way into a thread soon. For now, I'm off to digest some IFERROR, INDEX, and MATCH

Take care -

omni