Formula needed to extract data from a table based on 3 variables

**SparkyG** · 02-01-2017, 10:25 AM

Hi all
I'm new to this forum and an intermediate Excel User.

I have a problem I'm trying to solve and so far drawing blanks despite much research

Essentially I'm trying to put a number from a table based on drop down selections. Easy enough under normal circumstances, but the issue here is the table itself.
I'll try to explain:
the rows are product names - 50 plus products.
then following this there are pairs of columns for each country (multiple countries) so the Column header is for example Germany and that would appear in 2 columns, then each country has 2 use cases (A & B). so essentially we have a column with a header Germany, then in the row under it is "Use case A". the next column is also header Germany but the next Row is Use case B. , Then the other countries also have 2 columns and both use cases.

So based on selections made in drop down to linked cells I need to return the value in the cell identified by the product, then the country, then the use case within that country.

Hope that makes sense. I looked at Vlookup Match, and Index Match but neither seem to work

thanks for the help

Sparky

**zbor** · 02-01-2017, 10:29 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

**MrShorty** · 02-01-2017, 12:26 PM

I would be curious what variations of VLOOKUP(...MATCH()...) and INDEX(...MATCH(),MATCH()) you tried, because something like that is what I would expect to use. As one who does not like to multiple levels of nesting (especially while developing and debugging a sequence of formulas), I would expect to use:

1) A MATCH() function based on product name to return the row number.
2) A second MATCH() function based on country name to return column number.
3) A conditional function (such as an IF() function) to add 0 or 1 to the column number based on the use case
4) An INDEX() function using those results to pull out the desired value.

Where do you get stuck?

**SparkyG** · 02-02-2017, 07:43 AM

Thanks for the reply,

this is what I have tried so far:
Info:
C303 is the product selected by drop down
C312 is the country selected by drop down
C370 is the use case selected by drop down.
"Return Rates Master" is where the data table is
A7:P7 are the country names so B7 & C7 are one country , D7&E7 are another etc
B8:P8 are the use cases so B8 is use case 1, C8 is Use Case 2, etc

I tried a couple of things (please don't laugh

)

this first one was my starting point and it works but only gives me use case 1 as it selects the first entry of the country in each country pair (as I expected it to do:
=VLOOKUP(C303,'Return Rates Master'!A9:AE89,MATCH(C312,'Return Rates Master'!A7:P7,0),FALSE)

so I tried to expand on it by adding another match function to select the use case:

=VLOOKUP(C303,'Return Rates Master'!A9:AE89,MATCH(C312,'Return Rates Master'!A7:P7,MATCH(C370,'Return Rates Master'!B8:P8,0)))
My thoughts were the second match would then identify which use case within the country.
I think I'm close

Thanks again for the help, I really appreciate it

Sparky

**MrShorty** · 02-02-2017, 12:29 PM

=VLOOKUP(C303,'Return Rates Master'!A9:AE89,MATCH(C312,'Return Rates Master'!A7:P7,MATCH(C370,'Return Rates Master'!B8:P8,0)))
My thoughts were the second match would then identify which use case within the country.

Except that you have not programmed the second MATCH() to do that. Your second MATCH() function is in the "match_type" argument for the first MATCH(), so it is not going to help identify where the use case is in the table. (one of the things I hate about big, multi-level-nested functions is trying to keep track of what argument is where in such a mass of text).

Rather than put the second MATCH() inside of the first MATCH(), I would have added the result of the second MATCH() to the first MATCH() (subtracting 1 because the second match will not return 0) =VLOOKUP(...,...,MATCH(C312,'Return Rates Master'!A7:P7,0)+MATCH(C370,'Return Rates Master'!B8:C8,0)-1,...)