+ Reply to Thread
Results 1 to 2 of 2

Match two criteria to return one cell data

  1. #1
    Registered User
    Join Date
    08-16-2012
    Location
    Warrington, England
    MS-Off Ver
    Excel 2003
    Posts
    1

    Question Match two criteria to return one cell data

    The spreadsheet has 5 columns, two of which I need to match the creiteria and bring back the data from a third cell. The spreadsheet itself contains over 60k line of data!

    These are the headers on the spreadsheet. I need to match the computer name and the Item and bring back the data in value.

    tComputerName tItem tAssetID tValue tType

    The spreadsheet I need the returning cell info is on a separate spreadsheet and the matching criteria in on both axis. I have tried the formulas below but they don't seem to work?

    help please!

    {=INDEX(E2:E68826,MATCH(IF(A2:A68826=G10,IF(B2:B68826=M4,1)),0))}

    =IF(ISNA(INDEX(A4:E68828,MATCH(1,(G10=A:A)*(M6=B:B),0))),"",INDEX(A4:E68828,MATCH(1,(G10=A:A)*(M6=B:B),0)))

  2. #2
    Registered User
    Join Date
    08-06-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Match two criteria to return one cell data

    Here's a terrible workaround but I'm still trying to figure out a similar problem. Add an additional column with the numerical value of each row. i.e. F2 as 2, F3 as 3 and so on. Then with G2 and H2 as the parameters of your search put in this formula

    =INDEX(A2:F11,SUMPRODUCT((A2:A11=G2)*(B2:B11=H2)*(F2:F11)),4)

    The Sumproduct gives you the row number by taking it from Column F. This is put into the standard INDEX Formula and hey ho it's a messy solution to a pain of a problem.

    I'll keep an eye on this thread as I'd love to know the answer.

    Good luck
    Last edited by Cutter; 08-16-2012 at 07:45 PM. Reason: Removed double post

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1