# VLOOKUP an item that has more than one matching value?

1. ## 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. ## 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...

3. ## 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. ## 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.

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. ## 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. ## 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. ## Re: VLOOKUP an item that has more than one matching value?

You're welcome.

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.

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

#### 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 (?)
Template Usage (34):
• (1)footer
• (1)forumrules
• (1)gobutton
• (7)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (1)postbit_attachment
• (7)postbit_legacy
• (7)postbit_onlinestatus
• (7)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
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
• ./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_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_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• 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