+ Reply to Thread
Results 1 to 6 of 6

LOOKUP and IF Combined

  1. #1
    Registered User
    Join Date
    09-30-2013
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    2

    LOOKUP and IF Combined

    Hi All

    I hope you can help me if possible

    I am trying to workout the correct formula to work out pricing on a customer order sheet

    We have two pricing structures....price and sale

    I want to be able to have a drop down on the product (which I have set using validation) so that it will fill out the price depending on what the pricing structure is set at

    for example

    if K16 = shovel and D9 = sale the value in L15 should be 12
    if K16 = shovel and D9 = price the value in L15 should be 2


    any help would be greatly appreciated as I am very new to doing formulae in excel


    Thanks

    Adam

    tester.xlsx

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: LOOKUP and IF Combined

    Try this...

    =VLOOKUP(K16,D11:F14,IF(D9="Price",2,3),0)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: LOOKUP and IF Combined

    Try this:

    =VLOOKUP(K16,D11:F14,MATCH(D9,D10:F10,),)

  4. #4
    Registered User
    Join Date
    09-30-2013
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: LOOKUP and IF Combined

    Thanks for the reply guys it help greatly I just no have to figure out how to add a surcharge for colour options

    For Black the price is as per the pricing structures

    Blue,Red and white are +10%
    Union Jack is +25%

    any help again would be massively appreciated
    Attached Files Attached Files

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: LOOKUP and IF Combined

    If I understand what you're asking, something like this...

    =VLOOKUP(K16,D11:F14,IF(D9="Price",2,3),0)*IF(C15="Black",1,IF(OR(C15={"Blue","Red","White"}),1.1,IF(C15="Union Jack",1.25,0)))

  6. #6
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: LOOKUP and IF Combined

    Please check the files, i'm just perfect it the formula as you need

    Azumi
    Attached Files Attached Files

+ 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] Use selections from two data validation lists to lookup and return a combined result
    By lethonius2k in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-20-2013, 11:39 AM
  2. Slope function combined with some sort of lookup
    By Evetsllub in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-30-2013, 01:30 PM
  3. Replies: 4
    Last Post: 05-31-2012, 04:19 PM
  4. V-Lookup - Combined Lookup Values (2+)
    By Kolin in forum Excel General
    Replies: 2
    Last Post: 07-22-2010, 02:36 PM
  5. Array formula combined with Lookup
    By Kevin Gallagher in forum Excel General
    Replies: 9
    Last Post: 03-02-2006, 02:55 AM

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