Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Return Value After Matching Two Variables

1. ## Return Value After Matching Two Variables

Hi

I am attaching a sheet wherein I have tried to match two variables and to return the appropriate value. But I find the formula returns the value which one cell below the correct answer. Can anyone help?

Warm Regards
Srinivasan K

2. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Return The Value After Matching Two Variables

Not sure the syntax is right here, but as you have it, the problem is caused by matching the "mum" in D9. I would use SUMPRODUCT:

=SUMPRODUCT((D9:D14=D18)*(E9:E14=D19)*(F9:F14))
Last edited by Dave Hawley; December 11th, 2006 at 10:06.

Excel Video Tutorials / Excel Dashboards Reports

3. ## Re: Return The Value After Matching Two Variables

Hi

Thanks. As you said, Sumproduct will work here only if I have unique values (no repeated values for a same combination). If I have multiple values for a combination, this will result in throwing the sum of values.

If I want to display the value for the fist appearance of this combination, how can I achieve it.

Do you remember, you said about looping Vlookup up one after the other for a same kind of scenario, long back. If you could explain how Lookup formulae can be looped, it would be of much use.

I also would like to know, what makes this function to throw a value which is exactly below the right answer.

4. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Return The Value After Matching Two Variables

I can't see how this should work - it's not a formula I use. However, I CAN see what is wrong with your formula. You are using two MATCH functions connected with a + sign. Al this does is add the row numbers, and, since the first match of "mum" is in D9, whose index is 1, your result is coming from (5+1) = row 6.

The nesting of MATCH is dealt with here http://www.ozgrid.com/Excel/left-lookup.htm
and shows the MATCH functions connected with a comma. Also, note that these MATCHs are specifying the row number in an array. In your case the array would be D9:F14, and you would need to specify a column number, 3. You are using a single-dimension array, F9:F14, and defaulting the column number, and it may be that this form of INDEX doesn't work this way - I can't get it to work anyway. Perhaps someone with more exoerience can sort this out.

Excel Video Tutorials / Excel Dashboards Reports

5. ## Re: Return The Value After Matching Two Variables

Personally I would base a Pivot Tables off a Dynamic Named Range and move LOC and SYS columns into the page field.

Or, in Enter =D9&E9 in C9 and Fill Down. Then use See VLOOKUP and/or INDEX/MATCH

6. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: Return The Value After Matching Two Variables

Hi Srini,

=INDEX(F9:F14,MATCH(D18&D19,D9:D14&E9:E14,0))

To Enter the array formula hold down Ctrl and Shift while pushing Enter.

HTH

7. ## Re: Return Value After Matching Two Variables

Great Thanks to KK, Dave and BytheCringe2.

HAVE A GREAT DAY.

8. I agreed to these rules
Join Date
6th September 2011
Posts
1

## Re: Return The Value After Matching Two Variables

Originally Posted by Krishnakumar
Hi Srini,

=INDEX(F9:F14,MATCH(D18&D19,D9:D14&E9:E14,0))

To Enter the array formula hold down Ctrl and Shift while pushing Enter.

HTH
Dear Krishnakumar
I have a same problem but I have many equals data and I want to find the largest or smallest value. Below is problem that you solved, but assume we have another row with same data on first and second, now I want to find 22 not 19.
this is the problem befor(solved):

 mum acs 12 koc acs 13 chn acs 15 mum bom 19 koc bom 20 chn bom 11

And this is my problem:

 mum acs 12 koc acs 13 chn acs 15 mum bom 19 koc bom 20 chn bom 11 mum bom 22

Sincerely Yours
Masoud

Excel Video Tutorials / Excel Dashboards Reports

9. .
Join Date
1st September 2010
Posts
10,912

## Re: Return Value After Matching Two Variables

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno