+ Reply to Thread
Results 1 to 14 of 14

LOOKUPS from multiple rows and columns to give answers in certain cells

  1. #1
    Registered User
    Join Date
    04-26-2015
    Location
    Adelaide, South Australia
    MS-Off Ver
    2007
    Posts
    37

    LOOKUPS from multiple rows and columns to give answers in certain cells

    Hi All,

    I need some major help on LOOKUP's, I cant get my head around them. I have attached a sample worksheet to this post, it's mostly the lookup's and a few other things i need help with. (Detailed help description in the attachment).

    The short story is I have specific cells that say one thing and depending on what those cells say it will lookup what needs to be put into the other cells plus some other calculations are involved it's hard to explain here but if you had a look at the attachment you might understand it better.

    Thankyou.
    Attached Files Attached Files

  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: LOOKUPS from multiple rows and columns to give answers in certain cells

    Try this to start with...
    D2=INDEX($B$12:$D$15,MATCH($B2,$A$12:$A$15,0),MATCH(F$2,$B$10:$D$10,0))
    E2=INDEX($E$12:$G$15,MATCH($B2,$A$12:$A$15,0),MATCH(F$2,$E$10:$G$10,0))
    copied down

    Can you show some sample answers for the other part?
    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
    04-26-2015
    Location
    Adelaide, South Australia
    MS-Off Ver
    2007
    Posts
    37

    Re: LOOKUPS from multiple rows and columns to give answers in certain cells

    For the second Part:

    If the control is "CO" for a 127 on a 1200 width then the Number of slats is 11 for LHC, RHC and CS but i need to be 12 for "CO".

  4. #4
    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,323

    Re: LOOKUPS from multiple rows and columns to give answers in certain cells

    I think the next requirement needs this in D2:

    =IF(AND(G2="CO",ISODD(INDEX($B$12:$D$15,MATCH($B2,$A$12:$A$15,0),MATCH(F$2,$B$10:$D$10,0)))),INDEX($B$12:$D$15,MATCH($B2,$A$12:$A$15,0),MATCH(F$2,$B$10:$D$10,0))+1,INDEX($B$12:$D$15,MATCH($B2,$A$12:$A$15,0),MATCH(F$2,$B$10:$D$10,0)))

    As for the final requirement: could you talk us through an example?
    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.

  5. #5
    Registered User
    Join Date
    04-26-2015
    Location
    Adelaide, South Australia
    MS-Off Ver
    2007
    Posts
    37

    Re: LOOKUPS from multiple rows and columns to give answers in certain cells

    As for the final one,
    At the moment "D5" is 19 for number of slats with 17 - 111 / 1 - 108 Links but i need it add 64 to the width then divide the width by 2 and give me a new set of links.

  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,323

    Re: LOOKUPS from multiple rows and columns to give answers in certain cells

    Yes, but we need an example of what that means: please walk us through one example of adding 64 and dividing by 2. I don't know what those numbers represent ...

    OK - forget that - I understand now.

  7. #7
    Registered User
    Join Date
    04-26-2015
    Location
    Adelaide, South Australia
    MS-Off Ver
    2007
    Posts
    37

    Re: LOOKUPS from multiple rows and columns to give answers in certain cells

    Sorry i got what you mean now, Width is "2100" with 17 - 111 / 1 - 108 Links (add 64) "2164" (divide by 2) "1082" with new set of links 4 - 108 / 5 - 105. hope this helps.

  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,323

    Re: LOOKUPS from multiple rows and columns to give answers in certain cells

    No, I'm lost - sorry! I can't see that new set of links in your sample sheet: where is Excel meant to find it?

  9. #9
    Registered User
    Join Date
    04-26-2015
    Location
    Adelaide, South Australia
    MS-Off Ver
    2007
    Posts
    37

    Re: LOOKUPS from multiple rows and columns to give answers in certain cells

    Hi,

    I have attached a updated version of the sample with the missing link and width size.
    Attached Files Attached Files

  10. #10
    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,323

    Re: LOOKUPS from multiple rows and columns to give answers in certain cells

    Sorry - that file is exactly the same as the first.

  11. #11
    Registered User
    Join Date
    04-26-2015
    Location
    Adelaide, South Australia
    MS-Off Ver
    2007
    Posts
    37

    Re: LOOKUPS from multiple rows and columns to give answers in certain cells

    Sorry, I;ll try that again.
    Attached Files Attached Files

  12. #12
    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,323

    Re: LOOKUPS from multiple rows and columns to give answers in certain cells

    I am presuming this is what you want, although it won't work with a width of 1200, 1500 or 2800 as they aren't catered for in your lookup table.

    =IF(AND(G2="CO",ISODD(INDEX($B$11:$D$15,MATCH($B2,$A$11:$A$15,0),MATCH(F$2,$B$10:$D$10,0)))),INDEX($E$11:$G$15,MATCH(($B2+64)/2,$A$11:$A$15,0),MATCH(F$2,$E$10:$G$10,0)),INDEX($E$11:$G$15,MATCH($B2,$A$11:$A$15,0),MATCH(F$2,$E$10:$G$10,0)))

    You need to cater for 632, 782 and 932 as well.
    Last edited by AliGW; 06-02-2017 at 02:13 AM.

  13. #13
    Registered User
    Join Date
    04-26-2015
    Location
    Adelaide, South Australia
    MS-Off Ver
    2007
    Posts
    37

    Re: LOOKUPS from multiple rows and columns to give answers in certain cells

    Hi again,

    I want to say thankyou for all your help on this I really appreciate it,

    That last formula did work for the 127 but not the 89 or 100 as you can see in the attachment (Sample 2).
    Attached Files Attached Files

  14. #14
    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,323

    Re: LOOKUPS from multiple rows and columns to give answers in certain cells

    First of all, you do not appear to have added anything to the lookup table. Secondly, you know what your expected outcomes are - I don't - so without further enlightenment from you, I can't help tweak the formula. It does look as if you have not really properly thought this through: as I said before, your lookup table needs to cater for every eventuality, otherwise there will be nothing for the formula to return.

+ 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. Replies: 1
    Last Post: 05-28-2017, 09:41 PM
  2. Help with a formula to give multiple answers
    By buddydubbo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-10-2016, 09:59 PM
  3. [SOLVED] Looping list of points through function to give answers
    By Spicey_888 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-14-2015, 12:45 AM
  4. Replies: 1
    Last Post: 01-30-2014, 12:44 AM
  5. Multiple answers in Lookups
    By scmuddy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-19-2013, 10:11 AM
  6. Replies: 1
    Last Post: 08-14-2010, 10:33 PM
  7. Replies: 0
    Last Post: 01-10-2006, 10:28 AM

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