Need help with a lookup formula

H

Heaney

New Member
Joined
Sep 23, 2005
Messages
35
Office Version
  1. 365
Platform
  1. Windows
Below is an example of my data. Column H will have the same number repeated more than 1 time. What I'm trying to accomplish is to create a lookup based upon a value in Column H, and today's date to return the corresponding data in Column C. The data in this table is being pulled down from a SQL query.

Is this possible? If so, how would I approach this?


Book1
ABCDEFGHIJ
1Run Time (MST)Dealer Home Branch IDShipping Mailing NameDealer IDStock NumberOutbound Tracking NumberTitle StatusFolder NumberShipped byDealer Home Branch
22/16/2023 8:001138Dealer 1590528222R16004SAN JOSE
32/16/2023 8:001043Dealer 2579121224R45618NEW YORK MILLS
42/16/2023 8:001024Dealer 354783467R36404HOUSTON
52/16/2023 8:001086Dealer 462732785R34222SAN FRANCISCO
62/16/2023 8:001057Dealer 5323432343R36331SEATTLE
72/16/2023 8:001018Dealer 6373587907R24081NEW JERSEY
82/16/2023 8:001089Dealer 7526079150R22707KANSAS CITY
92/16/2023 8:001033Dealer 82995091750F33758DES MOINES
102/16/2023 8:001086Dealer 95100732130F30931SAN FRANCISCO
112/16/2023 8:001086Dealer 10619558168F17985SAN FRANCISCO
122/16/2023 8:001100Dealer 1164212618J25551PORTLAND
132/16/2023 8:001057Dealer 1264289231R31598SEATTLE
142/16/2023 8:001057Dealer 1264289233R7843SEATTLE
152/16/2023 8:001010Dealer 13592826204R21166CHARLOTTE
162/16/2023 8:001033Dealer 1464615438R45437DES MOINES
Sheet1
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
It should be possible with something like the FILTER function (example below).
The data you have above doesn't have any duplicates in column H. So I made Folder# 7843 a duplicate.
Also, you didn't show how you wanted show the result. In different rows as the example, across the columns are all in the same cell?

Book1
ABCDEFGHIJK
1Run Time (MST)Dealer Home Branch IDShipping Mailing NameDealer IDStock NumberOutbound Tracking NumberTitle StatusFolder NumberShipped byDealer Home Branch
22/16/23 8:00 AM1138Dealer 1590528222R16004SAN JOSE
32/16/23 8:00 AM1043Dealer 2579121224R45618NEW YORK MILLS
42/16/23 8:00 AM1024Dealer 354783467R36404HOUSTON
52/16/23 8:00 AM1086Dealer 462732785R34222SAN FRANCISCO
62/16/23 8:00 AM1057Dealer 5323432343R36331SEATTLE
72/16/23 8:00 AM1018Dealer 6373587907R24081NEW JERSEY
82/16/23 8:00 AM1089Dealer 7526079150R7843KANSAS CITY
92/16/23 8:00 AM1033Dealer 82995091750F33758DES MOINES
102/16/23 8:00 AM1086Dealer 95100732130F30931SAN FRANCISCO
112/16/23 8:00 AM1086Dealer 10619558168F17985SAN FRANCISCO
122/16/23 8:00 AM1100Dealer 1164212618J25551PORTLAND
132/16/23 8:00 AM1057Dealer 1264289231R31598SEATTLE
142/16/23 8:00 AM1057Dealer 1264289233R7843SEATTLE
152/16/23 8:00 AM1010Dealer 13592826204R21166CHARLOTTE
162/16/23 8:00 AM1033Dealer 1464615438R45437DES MOINES
17
18
19Todays date
202/16/2023 8:00
21Folder #
227843Dealer 7
23Dealer 12
Sheet1
Cell Formulas
RangeFormula
B22:B23B22=FILTER($C$2:$C$16,($A$2:$A$16=$A$20)*($H$2:$H$16=$A$22),"No Match")
Dynamic array formulas.
 
Upvote 0
Solution
AhoyNC said:
It should be possible with something like the FILTER function (example below).
The data you have above doesn't have any duplicates in column H. So I made Folder# 7843 a duplicate.
Also, you didn't show how you wanted show the result. In different rows as the example, across the columns are all in the same cell?

Book1
ABCDEFGHIJK
1Run Time (MST)Dealer Home Branch IDShipping Mailing NameDealer IDStock NumberOutbound Tracking NumberTitle StatusFolder NumberShipped byDealer Home Branch
22/16/23 8:00 AM1138Dealer 1590528222R16004SAN JOSE
32/16/23 8:00 AM1043Dealer 2579121224R45618NEW YORK MILLS
42/16/23 8:00 AM1024Dealer 354783467R36404HOUSTON
52/16/23 8:00 AM1086Dealer 462732785R34222SAN FRANCISCO
62/16/23 8:00 AM1057Dealer 5323432343R36331SEATTLE
72/16/23 8:00 AM1018Dealer 6373587907R24081NEW JERSEY
82/16/23 8:00 AM1089Dealer 7526079150R7843KANSAS CITY
92/16/23 8:00 AM1033Dealer 82995091750F33758DES MOINES
102/16/23 8:00 AM1086Dealer 95100732130F30931SAN FRANCISCO
112/16/23 8:00 AM1086Dealer 10619558168F17985SAN FRANCISCO
122/16/23 8:00 AM1100Dealer 1164212618J25551PORTLAND
132/16/23 8:00 AM1057Dealer 1264289231R31598SEATTLE
142/16/23 8:00 AM1057Dealer 1264289233R7843SEATTLE
152/16/23 8:00 AM1010Dealer 13592826204R21166CHARLOTTE
162/16/23 8:00 AM1033Dealer 1464615438R45437DES MOINES
17
18
19Todays date
202/16/2023 8:00
21Folder #
227843Dealer 7
23Dealer 12
Sheet1
Cell Formulas
RangeFormula
B22:B23B22=FILTER($C$2:$C$16,($A$2:$A$16=$A$20)*($H$2:$H$16=$A$22),"No Match")
Dynamic array formulas.
Click to expand...
Thank you AhoyNC, this worked. I was trying to solve through xlookup and wasn't getting anywhere.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Index Match function List based on date
Replies
7
Views
271
Fluff
Fluff
J
  • Question Question
Multiple Layer VLOOKUP, or better alternative?
Replies
9
Views
611
JukeExcel
J
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
901
paulkc
P
G
  • Question Question
Sorting data from another sheet to display in a main page
Replies
0
Views
450
Gonney
G
H
  • Question Question
Excel Table Representation Query: 2 columns in the data set with dates which needs to be aligned to get right result.
Replies
1
Views
566
GraH
GraH

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top