+ Reply to Thread
Results 1 to 15 of 15

vlookup or index and match

  1. #1
    Registered User
    Join Date
    07-15-2015
    Location
    new hampshire
    MS-Off Ver
    Office 2010
    Posts
    83

    vlookup or index and match

    Hi,

    On the input tab I would like to -

    When "CGLS" is selected in AB10 and "SLW2NV" (which is a sku from sku tab in column A) is selected, return "SLW2NV" in A26.

    When "CGLP" is selected in AB11 and "PLW2NV" (which is a sku from sku tab in column A) is selected, return "PLW2NV" in A27.

    The idea is that when a certian sku is keyed on input tab, and the CGL indicator in column AB is selected, the sku $ itself is returned in a certain cell.

    Note - this formula would go in the following ranges: A26:A29, G26:G29, M26:M29, S26:S29.

    Thank you!

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: vlookup or index and match

    Is the selection limited to only these SKUs i.e. "SLW2NV", "PLW2NV".

    And is the "Input SKU" either column E ("SLW2NV") or column F ("PLW2NV")

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: vlookup or index and match

    ... In your example both SKUs are in A26/A27... what criteria is used to put them in the other cells (G,M or S)?

  4. #4
    Registered User
    Join Date
    07-15-2015
    Location
    new hampshire
    MS-Off Ver
    Office 2010
    Posts
    83

    Re: vlookup or index and match

    Good question -
    No.

    It is any sku in Column A on Sku tab if you chose non blanks on F column

  5. #5
    Registered User
    Join Date
    07-15-2015
    Location
    new hampshire
    MS-Off Ver
    Office 2010
    Posts
    83

    Re: vlookup or index and match

    That is a tough one.

    The sku could be put in under Shirts B8 under SKU E9, or Pants G8 under SKU J9, Jackets L8 under O9, Coveralls Q8 under T9, and Other V8 under Y9.

    But - Column B on SKU tab shows those names (Shirts, Pants, etc.) as well.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,421

    Re: vlookup or index and match

    In what way does this query differ from your previous thread, please?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  7. #7
    Registered User
    Join Date
    07-15-2015
    Location
    new hampshire
    MS-Off Ver
    Office 2010
    Posts
    83

    Re: vlookup or index and match

    Which thread please?

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,421

    Re: vlookup or index and match

    This one:

    https://www.excelforum.com/excel-for...x-formula.html

    All of your threads seem to be much the same. If you have follow-on questions about the same issue, you should continue in one thread. It's all getting a bit confusing!

  9. #9
    Registered User
    Join Date
    07-15-2015
    Location
    new hampshire
    MS-Off Ver
    Office 2010
    Posts
    83

    Re: vlookup or index and match

    I see.
    Ok.

    Yes this one is totally new.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: vlookup or index and match

    Looking at your file: is it likely that there will more than 4 SKUs in any ONE of the input data (Shirts, Pants, etc) where the code in AB10:AB20 is "CGLS" or "CGLP".

    If not, then the SKUs could "moved" into the appropriate cells in rows 26:29 so "shirts" into A26:A29, "Pants" into G26:G29 etc

  11. #11
    Registered User
    Join Date
    07-15-2015
    Location
    new hampshire
    MS-Off Ver
    Office 2010
    Posts
    83

    Re: vlookup or index and match

    Thank you John!
    I dont understand, could you explain a bit?

    Thank you!

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: vlookup or index and match

    Easier if you post a file with the likely maximum of data in rows 10 to 20 with AB10:AB20 having the "CGLx" codes

  13. #13
    Registered User
    Join Date
    07-15-2015
    Location
    new hampshire
    MS-Off Ver
    Office 2010
    Posts
    83

    Re: vlookup or index and match

    There are 66 possible skus a denoted in Column C in yellow/with the CGL"X" indicator found in AB10:AB:20

    I also highlighted the destination cells in yellow in rows 29:32 on input tab.

    Thank you!

    This reply has a new file.

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: vlookup or index and match

    To me, there are appear to be inconsistencies in various formulae:

    in D10:

    =IF(E10=0,0,IF($AB10="CGLS",VLOOKUP($E10,SKU!$A$1:$G$2005,7,FALSE),VLOOKUP($E10,SKU!$A$1:$G$2005,6,FALSE)))

    in B29

    =IF(A29=0,0,(VLOOKUP(A29,SKU!$A$1:$F$1620,6,FALSE)))

    The latter should be (I think!) as below based on the "CGL" selection

    =IF(A29=0,0,(VLOOKUP(A29,SKU!$A$1:$F$1620,7,FALSE)))

    And I am not sure how (for me) we can easily transfer the entries in rows 10-20 to 29-32: you have 5 "blocks" (Shirts/Pants etc) in rows 10-20 but only 4 "blocks" in rows 29-32 so originally I was going to do a simple transfer from E to A, J to G etc.

    Other observationS;

    why not a simple "GCL" indicator for all goods? As it stands, you have offset order lines to correspond to the "GCLxx" code.

    All seems overly complex to me.

  15. #15
    Registered User
    Join Date
    07-15-2015
    Location
    new hampshire
    MS-Off Ver
    Office 2010
    Posts
    83

    Re: vlookup or index and match

    I see you challenge -
    Let me respond by walking you through the entire process -

    For the 66 possible skus in column C from Sku tab - we get two different unit rates (C10 and C29).

    When the user chooses a SKU that is part of the 66 possible skus in column C from Sku tab, inputs that sku in E10 on input tab, and then chooses CGLS (if a pant sku CGLP, if a jacket CGLS, if a coverall CGLC, if a "other" CGLO) from the pull down list in column AB on the input tab.....the costs in I10 on Input tab change to the costs in Column G vs Column F on Sku tab.

    I want the same sku that was manually keyed into E10 to return in any of the cells highlighted in yellow (A29:A32 or G29:G32 or M29:M32 or S29:S32).

    Does this make sense.

+ 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. VLOOKUP or INDEX/MATCH with multiple column index numbers
    By cerebral87 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-22-2017, 07:13 PM
  2. [SOLVED] INDEX+MATCH instead of VLOOKUP+MATCH, why is INDEX a better choice and how to re-write?
    By Renejorgensen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-23-2016, 10:54 AM
  3. Replies: 1
    Last Post: 06-18-2015, 08:45 AM
  4. Replies: 2
    Last Post: 12-18-2014, 09:52 AM
  5. Replies: 3
    Last Post: 05-19-2014, 02:01 PM
  6. Replies: 13
    Last Post: 12-13-2012, 11:44 AM
  7. Replies: 2
    Last Post: 03-16-2012, 12:03 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