+ Reply to Thread
Results 1 to 8 of 8

If (multiple values) and vlookup (based on answer to multiple values)

  1. 07-30-2018, 06:33 AM #1
    MaverickSemperFi
    MaverickSemperFi is offline
    Registered User
    Join Date
    02-05-2018
    Location
    Manchester
    MS-Off Ver
    2016
    Posts
    12

    If (multiple values) and vlookup (based on answer to multiple values)

    Hi All,

    I'm having trouble with a spreadsheet I'm putting together and hoping you can all help.

    I basically have a list of source information that I need returning into a list I have created based on my own values.

    For example, If the ref in A2 (My Sheet) is equal to A2 (Source) and B2 (My Sheet) is nearest to or within like 10-15% of E2 (Source) then it returns the Cost (associated with that outcome).

    I have tried a variety of If formulas and Vlookup forums but can't seem to crack it. I would really appreciate the help it's driving me mad.

    Please find attached a test sheet which also includes text as to what I'm after.

    If you require any further information to help solve the issue, please let me know.


    Kind Regards,

    MaverickSemperFi
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-30-2018, 06:36 AM #2
    AliGW
    AliGW is online now
    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
    80,980

    Re: If (multiple values) and vlookup (based on answer to multiple values)

    Sorry, but what exactly does this mean?

    I want this to be something like - if(j7=something in column J and k7 is within near too f7 then returns cost
    'Something like' doesn't really help us unless you tell us what you are thinking.

    It would be MUCH easier if you mocked up the workbook with your expected outcomes entered manually and let us work out the formulae required.
    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.
    Reply With QuoteRegister To Reply
  3. 07-30-2018, 06:48 AM #3
    MaverickSemperFi
    MaverickSemperFi is offline
    Registered User
    Join Date
    02-05-2018
    Location
    Manchester
    MS-Off Ver
    2016
    Posts
    12

    Re: If (multiple values) and vlookup (based on answer to multiple values)

    Apologies for the confusion.

    I have attached another sheet with what my expected outcome would be.

    Kind Regards,

    MaverickSemperFi
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 07-30-2018, 07:11 AM #4
    AliGW
    AliGW is online now
    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
    80,980

    Re: If (multiple values) and vlookup (based on answer to multiple values)

    I don't understand your first answer. Surely 75 is closer to 78 than 68 is, so why isn't the first answer 200.00?
    Reply With QuoteRegister To Reply
  5. 07-30-2018, 07:14 AM #5
    MaverickSemperFi
    MaverickSemperFi is offline
    Registered User
    Join Date
    02-05-2018
    Location
    Manchester
    MS-Off Ver
    2016
    Posts
    12

    Re: If (multiple values) and vlookup (based on answer to multiple values)

    Sorry, yes you are right. I had changed a variety of the values in the sources and missed that one. But yes that is the idea behind it .
    Reply With QuoteRegister To Reply
  6. 07-30-2018, 07:16 AM #6
    AliGW
    AliGW is online now
    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
    80,980

    Re: If (multiple values) and vlookup (based on answer to multiple values)

    I'm not at all sure about any of your calculations, then, but try this as a starting point:

    =LOOKUP(2,1/((Source!$E$2:$E$32>=B2*0.85)*(Source!$A$2:$A$32='My Sheet'!A2)),Source!$G$2:$G$32)
    Reply With QuoteRegister To Reply
  7. 07-30-2018, 07:24 AM #7
    MaverickSemperFi
    MaverickSemperFi is offline
    Registered User
    Join Date
    02-05-2018
    Location
    Manchester
    MS-Off Ver
    2016
    Posts
    12

    Re: If (multiple values) and vlookup (based on answer to multiple values)

    Really appreciate it thank you
    Reply With QuoteRegister To Reply
  8. 07-30-2018, 07:26 AM #8
    AliGW
    AliGW is online now
    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
    80,980

    Re: If (multiple values) and vlookup (based on answer to multiple values)

    Not a problem - I hope you are able to adapt it to do what you want.

    Let us know how you get on.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Vlookup return multiple values horizontally for a list of values
    By lucipurr in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-07-2018, 11:00 AM
  2. [SOLVED] sum of vlookup of multiple values based on 2 criteria
    By m.wiener91 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-10-2017, 06:47 PM
  3. Vlookup multiple Values then Concatenate all values in One cell
    By Neyme in forum Excel General
    Replies: 10
    Last Post: 07-25-2015, 01:41 PM
  4. [SOLVED] Use Vlookup to return minimu values when the are multiple minimum values in a range
    By trandle in forum Excel General
    Replies: 13
    Last Post: 10-13-2014, 02:00 AM
  5. Vlookup multiple Values then Concatenate all values in One cell
    By Neyme in forum Excel General
    Replies: 3
    Last Post: 07-24-2014, 01:58 AM
  6. VLOOKUP Multiple Values then give answer based on condition
    By Wetfloor in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-07-2014, 10:24 AM
  7. Multiple vlookup values based on text in another cell
    By jmewebb in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-19-2014, 03:24 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1