+ Reply to Thread
Results 1 to 6 of 6

Bring in data from one tab based on two sets of criteria on a different tab

  1. #1
    Registered User
    Join Date
    01-06-2012
    Location
    MN
    MS-Off Ver
    Excel 2013
    Posts
    62

    Bring in data from one tab based on two sets of criteria on a different tab

    I am trying to come up with a way to use the Prod. Code and Pkg information on the pricing tab to bring in the corresponding margin from the margin tab. For example, I'd like the program to bring in $124 into cell K5 on the pricing tab (program looks up or matches that Prod code of 3890 with Pkg of 200 lb Bio and finds the $123 in cell E2 on the Margin tab). I have suspect that a combination of MATCH, AND, IF and perhaps a Vlookup will get the job done but I have been unsuccessful in my attempts and would greatly appreciate any and all help.

    tpk working sheet.xlsx

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Bring in data from one tab based on two sets of criteria on a different tab

    Because you have mixed numeric/text cells, this became a bit more involved.

    Also, the text entries on sheet2 have trailing spaces, and the "numbers" on sheet1 are actually text that just looks like a number

    Try this, copied down...
    =INDEX(Margins!$A$1:$F$5,MATCH(IFERROR(Pricing!A3*1,A3&"*"),Margins!$A$1:$A$5,0),MATCH(Pricing!D3,Margins!$A$1:$F$1,0))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-06-2012
    Location
    MN
    MS-Off Ver
    Excel 2013
    Posts
    62

    Re: Bring in data from one tab based on two sets of criteria on a different tab

    FDibbins - THANK YOU! Your solution works perfectly. The entries you reference on sheet are out of AX and it is more trouble to try to change AX than to deal with the text/data issues in Excel. I can follow along with the formula except for Pricing!A3*1,A3&"*" So I can do this the next time, what does that part of the formula do? I have not used a cell times one in combination with the same cell listed with & followed by what I think is a wildcard?

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Bring in data from one tab based on two sets of criteria on a different tab

    This part...
    MATCH(IFERROR(Pricing!A3*1,A3&"*"),Margins!$A$1:$A$5,0)
    is determining which row to use. It would normally just be....
    MATCH(A3,Margins!$A$1:$A$5,0)
    but some rows have text and some have values - although the values themselves are text, so 1st I test for numeric entry...
    Pricing!A3*1........multiplying a text numeric with 1 (or adding 0) converts it to a real number
    IFERROR works by testing the 1st option for TRUE/FALSE, if TRUE, it uses that option, it FALSE, it used the 2nd option
    IFERROR( Pricing!A3*1, A3&"*")...
    This is actually 2 different arguments
    - Pricing!A3*1 is converting text numeric to numeric
    - A3&"*" is adding a wild-card to the search criteria for the 2nd argument
    because the data on sheet1 has trailing spaces, I set it up to look for A3 and whatever followed it - all those spaces

    Hope that helped?
    Last edited by FDibbins; 11-26-2013 at 12:13 PM.

  5. #5
    Registered User
    Join Date
    01-06-2012
    Location
    MN
    MS-Off Ver
    Excel 2013
    Posts
    62

    Re: Bring in data from one tab based on two sets of criteria on a different tab

    It helps tremendously and I will be able to use this in the future. Thanks again!

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Bring in data from one tab based on two sets of criteria on a different tab

    You are most welcome, and thank you for the feedback

+ 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. Copy and Paste rows to another spreedsheet based on multiple sets of criteria.
    By juhaszp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-25-2013, 11:08 AM
  2. [SOLVED] Bring back a name in one column based on criteria in 2 other columns
    By BillDoor in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-22-2013, 08:07 AM
  3. bring back cell on closed workbook based on 2 sets of information
    By scruffpitt in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-15-2012, 06:19 PM
  4. Counting data sets based on multiple criteria
    By hjb0802 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-29-2010, 10:47 AM
  5. Counting a column based on two sets of criteria
    By Cjax in forum Excel General
    Replies: 4
    Last Post: 07-23-2009, 02:40 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