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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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
326
Fluff
Fluff
J
  • Question Question
Multiple Layer VLOOKUP, or better alternative?
Replies
9
Views
632
JukeExcel
J
A
  • Solved
Match three columns together across sheet to subtraction based invoice number
2
Replies
10
Views
225
abdo meghari
A
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
934
paulkc
P
G
  • Question Question
Sorting data from another sheet to display in a main page
Replies
0
Views
453
Gonney
G

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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