+ Reply to Thread
Results 1 to 3 of 3

Use Index/Match to return a value based on two criteria? Not working for me...

  1. #1
    Registered User
    Join Date
    11-17-2015
    Location
    Iowa, USA
    MS-Off Ver
    2010
    Posts
    8

    Use Index/Match to return a value based on two criteria? Not working for me...

    I have a file with two tabs (Final Calc and Initial Data). I am trying to pull back a field from Final Calc based on it matching two data fields on the Initial Data tab. Here is a basic example:

    Final Calc Tab

    Transaction #/ Price Variance / New Price
    123 / 0.125 / 102.00
    123 / 0.307 / 101.50
    456 / 0.50 / 103.00
    456 / 1.25 / 99.875
    657 / 0.652 / 98.52
    657 / 0.75 / 100.50

    Initial Data Tab

    Transaction #/ Price Variance(Min)/ New Price
    123 / 0.125 / value I want from the other tab
    456 / 0.50
    657 / 0.652

    I have already pulled back the minimum values from final calc tab, I just need to bring in the new price value based on matching to transaction and the price variance on the initial data tab.

    I tried using an Index match with two criteria but can't see to get the formula to work. Here is what I had:

    =INDEX('Final Calc'!A1:Q21511, MATCH(1,'Final Calc'!A:A='Initial Data'!A2)*('Final Calc'!Q:Q='Initial Data'!P2),0),14)

    I need this to be an exact match too. I also did the CTRL SHIFT ENTER when I was done with this but am getting an error saying there are errors in my formula but I can't figure out where I went wrong.

    Thank you for the help!!

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Use Index/Match to return a value based on two criteria? Not working for me...

    Try that
    =INDEX('Final Calc'!A1:Q21511, MATCH(1,--('Final Calc'!A:A='Initial Data'!A2)*--('Final Calc'!Q:Q='Initial Data'!P2),0),14)
    (not tested)
    Last edited by José Augusto; 11-18-2015 at 02:14 PM.

  3. #3
    Registered User
    Join Date
    11-17-2015
    Location
    Iowa, USA
    MS-Off Ver
    2010
    Posts
    8

    Re: Use Index/Match to return a value based on two criteria? Not working for me...

    This worked as far as returning values. Once I started to do my analysis I noticed some of my values didn't seem correct. I had to go back and F4 on my equation to make sure it searching through the whole range input but once I did that it worked great.

    Thanks!!
    Last edited by ralldredge; 11-18-2015 at 03:35 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Combining 3 Formulas: Return all Names that Match Criteria Using Index/Match
    By bchilme in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 09-29-2014, 09:28 AM
  2. [SOLVED] INDEX MATCH trying to return a value between two criteria.
    By cyberkidkiller in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2014, 09:48 AM
  3. [SOLVED] Using Index Match with a Criteria to return a minimum
    By david0985 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-18-2014, 12:59 PM
  4. Index Match using two criteria to return on of criteria values
    By RogueLeader in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2014, 09:50 AM
  5. [SOLVED] Index and Match for 4 Criteria - My 2 attempts are not working
    By denise001 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-05-2013, 01:55 PM
  6. [SOLVED] Index Match Multiple Criteria not working
    By garciapliz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-22-2013, 12:07 PM
  7. [SOLVED] INDEX/MATCH not working for multiple criteria
    By lukesanborn in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-27-2012, 09:20 PM

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