+ Reply to Thread
Results 1 to 3 of 3

Match Index

  1. #1
    Registered User
    Join Date
    03-28-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    31

    Match Index

    Thank you guys for helping me with the formula
    Attached Files Attached Files
    Last edited by Pranav9940; 06-07-2013 at 12:58 AM. Reason: Changed Prefix

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Match Index

    Do you mean?

    =SUMPRODUCT(($A$4:$A$27=A36)*($B$4:$B$27=B36)*($C$4:$C$27))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Match Index

    There are two ways to go about this.
    Option 1: Insert a new column before A, moving everything over. In the new Col A
    =A4&B4 copied down. You can then Hide column A
    Your formula is then (what is now D35)
    =VLOOKUP(B35&C35, $A$4:$D$27, 4, FALSE)

    Option 2 (using array formulas) and INDEX/MATCH
    In C35
    =INDEX($C$4:$C$27, MATCH(A35&B35, $A$4:$A$27&$B$4:$B$27,0)) entered as an Array
    (Use CNTRL SHFT ENTER instead of ENTER. You'll see {} around your formula if done properly)

    Option 2 takes more memory and will slow your computer down if you have a lot of data. Best way to tell is to try it.
    Questions?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ 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