Lookup help, multiple columns
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Lookup help, multiple columns
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Lookup help, multiple columns

    I'm unsure what formula I need to return an adjacent value to the min value from a set of columns...

    My excel knowledge is very limited

    I have data in cells BC20 to CJ20 and down to BC136 and over to CJ136

    I have numbers in every other column and text in between

    example:
    BC20 has pricing
    BD20 has lead time
    BE20 has pricing
    BF20 has lead time
    and so on

    I have a formula in CK20 to pull min value, im sure this is probably not the easiest way but its all I know =MIN(BC20,BE20,BG20,BI20,BK20,BM20,BO20,BQ20,BS20,BU20,BW20,BY20,CA20,CC20,CE20,CG20,CI20)

    In column CL i pull in the vendor information from the column titles
    =INDEX($BC$19:$CB$19,MATCH(CK20,BC20:CB20,))

    Those seem to work great

    But my issue is that i need the corresponding lead time to pull into CM from the Min value i have in CK

    HELP PLEASE


  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,151
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Lookup help, multiple columns

    Try this

    In the CK20 cell you can put the array formula

    =MIN(IF(ISODD(COLUMN(BC20:CJ20)),BC20:CJ20))

    Array formulas
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself


    --------------
    in CM20:

    =INDEX(BD20:CJ20,MATCH(CK20,BC20:CI20,0))
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup help, multiple columns

    when i do that it puts nothing in CK and in CM it pulls my min,

    i need to note that some columns have no values because theres no price from vendor

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,151
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Lookup help, multiple columns

    Quote Originally Posted by texasduckhunter View Post
    when i do that it puts nothing in CK and in CM it pulls my min,

    i need to note that some columns have no values because theres no price from vendor

    Try this en CK20

    =MIN(IF(ISODD(COLUMN(BC20:CJ20)),IF(BC20:CJ20<>"",BC20:CJ20)))

    Is an array formula to accept must be press Shift + Control + Enter.
    Regards Dante Amor

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup help, multiple columns

    That seems to have done the trick!

    Thank you very much!

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,151
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Lookup help, multiple columns

    Quote Originally Posted by texasduckhunter View Post
    That seems to have done the trick!

    Thank you very much!
    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

  7. #7
    New Member
    Join Date
    Jul 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup help, multiple columns

    DanteAmor,

    you did so well the last time, i tried copying what you did with the following adjustments and it didnt work:

    I have data in cells CP20 to DW20 and down to CP135 and over to DW135

    I have numbers in every other column and text in between

    example:
    CP
    20 has pricing

    CQ
    20 has lead time

    CR
    20 has pricing

    CS
    20 has lead time

    and so on

    i need the same kind of help please.

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,151
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Lookup help, multiple columns

    Quote Originally Posted by texasduckhunter View Post
    DanteAmor,


    I have data in cells CP20 to DW20 and down to CP135 and over to DW135

    =MIN(IF(ISODD(COLUMN(CP20:DW20)),IF(CP20:DW20<>"",CP20:DW20)))

    Is an array formula to accept must be press Shift + Control + Enter.
    Regards Dante Amor

  9. #9
    New Member
    Join Date
    Jul 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup help, multiple columns

    there are some cells with no data, how does it skip those?

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,151
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Lookup help, multiple columns

    Quote Originally Posted by texasduckhunter View Post
    there are some cells with no data, how does it skip those?
    Example:

    2 Blank 3

    What's the result? 2 or blank?
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •