Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Return Value After Matching Two Variables

  1. #1
    Join Date
    12th January 2005
    Location
    India
    Posts
    78

    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
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    K. Srinivasan

  2. #2
    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. #3
    Join Date
    12th January 2005
    Location
    India
    Posts
    78

    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.

    K. Srinivasan

  4. #4
    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. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    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. #6
    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. #7
    Join Date
    12th January 2005
    Location
    India
    Posts
    78

    Re: Return Value After Matching Two Variables

    Great Thanks to KK, Dave and BytheCringe2.

    HAVE A GREAT DAY.

    K. Srinivasan

  8. #8
    Join Date
    6th September 2011
    Posts
    1

    Re: Return The Value After Matching Two Variables

    Quote Originally Posted by Krishnakumar View Post
    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
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


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

    Re: Return Value After Matching Two Variables

    Please do not post questions in threads started by other users.

    Start your own thread, give it an accurate and concise title and explain your issue fully. If you think any other thread can help clarify your problem then you can include a link by copying the URL from the address bar of your browser and pasting into your message.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Return All Matching Occurences
    By rpancheri in forum Excel General
    Replies: 4
    Last Post: April 16th, 2008, 10:00
  2. Return Matching Name To ID In TextBox
    By toast in forum Excel General
    Replies: 6
    Last Post: April 8th, 2008, 11:59
  3. Return Reference For Matching Text
    By SerenityNetwork in forum Excel General
    Replies: 2
    Last Post: July 19th, 2007, 04:37
  4. Return All Matching Results of a Lookup
    By wingam00 in forum Excel General
    Replies: 9
    Last Post: September 6th, 2006, 00:19
  5. VLOOKUP wont return the matching value
    By sebastia in forum Excel General
    Replies: 14
    Last Post: October 7th, 2004, 21:34

Bookmarks

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