+ Reply to Thread
Results 1 to 7 of 7

VLOOKUP an item that has more than one matching value?

  1. #1
    Registered User
    Join Date
    07-05-2018
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    8

    VLOOKUP an item that has more than one matching value?

    What's the best way to lookup a value from one table, that has multiple matching values in another table? Or, perhaps to word it another way, to lookup a value using more than one criteria?

    Let's say I have two tables, ProductSheet and DailyPrices. They look somewhat like this:

    DailyPrices:
    Date SKU Price
    11-Jul-18 SOX $5.25
    11-Jul-18 BLT $2.00
    12-Jul-18 HAT $4.00
    13-Jul-18 SOX $6.00

    ProductSheet:
    SKU Current Price
    SOX =Latest price in price list
    HAT =Latest price in price list
    BLT =Latest price in price list

    How can I lookup the *latest* price of an item in DailyPrices and return it as the current price in ProductSheet? I tried VLOOKUP but I believe what I'm looking to do is too complicated for VLOOKUP. I also tried a table-based query but couldn't get that to return what I needed either. Any suggestions? Thanks in advance!

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,847

    Re: VLOOKUP an item that has more than one matching value?

    Like this:

    =IFERROR(LOOKUP(2,1/($A$2:$A$14=F2),$B$2:$B$14),"")

    see file.

    File attached this time...
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    07-05-2018
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    8

    Re: VLOOKUP an item that has more than one matching value?

    Thanks Glenn for taking time out of your day to help me. My sample was a little simplistic, but I was able to combine what you gave me with a table-based query and get the results I'm looking for. Thank-you!

    That said, could you help me understand how it works? I believe the secret sauce is in this portion:

    =IFERROR(LOOKUP(2,1/($A$2:$A$14=F2),$B$2:$B$14),"")

    That doesn't seem to follow the syntax for LOOKUP() in the online help, any chance you can walk me through what you've done here? Thanks again for helping me solve my problem!

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,847

    Re: VLOOKUP an item that has more than one matching value?

    ($A$2:$A$14=F2)
    looks for a match for F2 in A2 to A14. It returns TRUE, FALSE,FALSE,TRUE,FALSE,FALSE,FALSE, etc to the end of the range.

    1/($A$2:$A$14=F2)
    returns 1,#DIV/0,1,#DIV/0,#DIV/0,#DIV/0,#DIV/0,etc

    LOOKUP(2
    You're looking for a value = 2. There can never be one. It'll either be 1 or #DIV/0. In this case, LOOKUP will match the last numeric value found in the array, which corresponds to the last instance of a match for SOX... and

    $C$2:$C$10)

    return the corresponding value from C2 to C10.

    The IFERROR bit returns a blank if no match is found.





    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: VLOOKUP an item that has more than one matching value?

    A couple of alternatives that don't rely on the dates being in ascending order.

    Based on the dailyprices data in columns A2:C5, criteria SKU in E2

    All versions of excel from 2007 onwards (this one must be array confirmed with Shift Ctrl Enter).

    =SUMIFS($C$2:$C$5,$B$2:$B$5,$E2,$A$2:$A$5,MAX(IF($B$2:$B$5=$E2,$A$2:$A$5)))

    Office 365 subscription version (untested, I don't have this version at home).

    =SUMIFS($C$2:$C$5,$B$2:$B$5,$E2,$A$2:$A$5,MAXIFS($A$2:$A$5,$B$2:$B$5,$E2))

  6. #6
    Registered User
    Join Date
    07-05-2018
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    8

    Re: VLOOKUP an item that has more than one matching value?

    Wow, that's... a little kludgey. But it works, thanks to everyone for taking the time to help!

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,847

    Re: VLOOKUP an item that has more than one matching value?

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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. Finding an item that isn't matching.
    By kylpr in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-07-2017, 04:42 PM
  2. [SOLVED] lookup if matching item and between dates
    By Sherk in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-13-2015, 12:04 PM
  3. [SOLVED] Formula needed - line item of unique item matching criteria
    By NS4Excel in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 04-18-2015, 06:32 PM
  4. Excel 2007 : Help matching data based on item number
    By SubtleFudge in forum Excel General
    Replies: 6
    Last Post: 04-10-2012, 04:42 PM
  5. Replies: 5
    Last Post: 07-26-2011, 10:35 PM
  6. Select combobox item using string matching
    By TheFormerAstronomer in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-26-2011, 04:27 AM
  7. Replies: 9
    Last Post: 05-21-2011, 12:14 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.07236 seconds
  • Memory Usage 9,102KB
  • Queries Executed 16 (?)
More Information
Template Usage (34):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (7)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (7)postbit_legacy
  • (7)postbit_onlinestatus
  • (7)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (40):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php
  • ./packages/vbforum/bbcodehelper/table.php 

Hooks Called (49):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • postbit_attachment
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1