+ Reply to Thread
Results 1 to 8 of 8

LOOKUP IF AND COMBO Help please...

  1. #1
    Registered User
    Join Date
    05-01-2014
    Location
    Bolton, UK
    MS-Off Ver
    Excel Mac 2011
    Posts
    3

    Unhappy LOOKUP IF AND COMBO Help please...



    Hi,

    I am new to the forum so forgive me if I break any 'rules'

    I have a problem I wonder if anybody can help with as I'm going around in circles.

    I have my excel spreadsheet set up as follows:

    Min Max (incl VAT)

    0.01 5.00 1.20
    5.01 10.00 1.40
    10.01 15.00 1.70
    15.01 20.00 1.80
    20.01 25.00 2.40
    25.01 30.00 2.80
    30.01 35.00 3.40

    I am looking for a formula that I can use that refers to this table.

    So, elsewhere on the spreadsheet, I want an 'input cell' where I can insert a price (lets say 7.32)
    I need a formula that will basically say, if 7.32 falls between range 0.01-5.00 (which it does't!) then display 1.20
    So because 7.32 falls between the range 5.01 and 10.00, I would like a value of 1.40 to be displayed so that I can use that in future calculations.

    I've tried =IF statements, and VLOOKUP and HLOOKUP, but none seem to fit my needs. (Plus I may be doing it wrong).

    Any help much appreciated - thanks,
    Phil

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,298

    Re: LOOKUP IF AND COMBO Help please...

    have you tried
    =vlookup(cell to lookup , range to lookup, 3 , true)

    where i'm assuming the table is in three columns
    and true will find the nearest lower value

    can you post a sample spreadsheet

    this is in a normal cell, not sure about a combo box though
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,544

    Re: LOOKUP IF AND COMBO Help please...

    If your data is in the range A2:C8 and your input cell is E2, try this array formula. Since this is an array formula so you need to confirm it with Ctrl + Shift + Enter instead of just Enter. (i.e. hold down the Ctrl+Shift and press Enter.)

    Please Login or Register  to view this content.
    Is this what you want?
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: LOOKUP IF AND COMBO Help please...

    This would work, assuming your table is on sheet2 in the range listed, and your input cell was A1 of whatever sheet.

    =SUMPRODUCT((Sheet2!A2:A8<=A1)*(Sheet2!B2:B8>=A1)*(Sheet2!C2:C8))
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  5. #5
    Registered User
    Join Date
    05-01-2014
    Location
    Bolton, UK
    MS-Off Ver
    Excel Mac 2011
    Posts
    3

    Re: LOOKUP IF AND COMBO Help please...

    Thanks very much for your rapid responses. These may very well work, but I don't think I have the experience to 'tweak' them.

    I've attached a sample spreadsheet which I'm sure will help you guys to help me.

    Hopefully the file has attached.
    Attached Files Attached Files

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,298

    Re: LOOKUP IF AND COMBO Help please...

    all three methods posted will work

    heres mine
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-01-2014
    Location
    Bolton, UK
    MS-Off Ver
    Excel Mac 2011
    Posts
    3

    Re: LOOKUP IF AND COMBO Help please...

    Pure genius - thank you all.

    You can't imagine the hours I spent trying to achieve that.

    1000 thankyous!


  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,298

    Re: LOOKUP IF AND COMBO Help please...

    your welcome ,thanks for the rep

+ 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] Lookup data with combo box value
    By sbabu16 in forum Excel General
    Replies: 2
    Last Post: 01-29-2014, 12:04 AM
  2. Combo Box Lookup Only
    By Mooseman60 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-09-2010, 05:30 PM
  3. Combo box lookup
    By jberr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-25-2010, 11:21 AM
  4. [SOLVED] lookup for a combo box in VBA
    By Jennifer in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-15-2005, 04:06 PM
  5. [SOLVED] Combo Box lookup
    By CEN7272 - ExcelForums.com in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-27-2005, 03:06 PM

Tags for this Thread

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