Formula needed to extract a value based off multiple criteria

ItalianPlatinum

Well-known Member
Joined
Mar 23, 2017
Messages
733
Office Version
  1. 2021
  2. 2019
  3. 2016
  4. 2010
  5. 2007
Platform
  1. Windows
Hello - Running into a head scratcher here. it seems easy but I am having trouble putting all the pieces together and hoping someone could help. I condensed my large data set into one tab for ease as a sample
  1. I have VBA code that will run 1 days worth of data if B11 = B12
  2. If B11 doesnt equal B12 then it will run for 2 sets of dates; once for B11 and once for B12. So duplicates will generate.
  3. Formula I am looking for is to lookup the price of a identifer where if C is blank it uses B11 as the date to lookup and if C has a value it uses B12 as a lookup
I can rearrange A:D to make the lookup easier. Any help is appreciated my dataset is 1,000 lines so manual approach is exhaustive. I am open for VBA or a formula. Thanks in advance.

Book1
ABCDEFGH
1DatePriceFri/SunIndentifierToday's DateIndentifierPrice
230-Oct10AAA30-OctAAAFormula
330-Oct15BBB30-OctBBBFormula
430-Oct20SCCC30-OctCCCFormula
530-Oct75SDDD30-OctDDDFormula
61-Nov20SCCC
71-Nov75SDDD
81-Nov10AAA
91-Nov15BBB
10
11Today's Date10/30/2020New Years Day1/1/2020
12Last BD - 1 or Holiday adjusted11/1/2020Martin Luther King, Jr. Day1/20/2020
13Washington's Birthday2/17/2020
14Good Friday4/10/2020
15Memorial Day5/25/2020
16Independence Day7/3/2020
17Labor Day9/7/2020
18Thanksgiving Day11/26/2020
19Christmas Day12/25/2020
Data
Cell Formulas
RangeFormula
B11B11=TODAY()-1
B12B12=WORKDAY(TODAY(),1,F11:F19)-1
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
test2.xlsx
ABCDEFGH
1DatePriceFri/SunIndentifierToday's DateIndentifierPrice
210/30/2010AAA10/30/20AAA10
310/30/2015BBB10/30/20BBB15
410/30/2020SCCC10/30/20CCC21
510/30/2075SDDD10/30/20DDD76
611/01/2021SCCC
711/01/2076SDDD
811/01/2011AAA
911/01/2016BBB
10
11Today's Date10/30/20New Years Day1/1/2020
12Last BD - 1 or Holiday adjusted11/01/20Martin Luther King, Jr. Day1/20/2020
13Washington's Birthday2/17/2020
14Good Friday4/10/2020
15Memorial Day5/25/2020
16Independence Day7/3/2020
17Labor Day9/7/2020
18Thanksgiving Day11/26/2020
19Christmas Day12/25/2020
Sheet1
Cell Formulas
RangeFormula
H2:H5H2=MAX(IF({1,0},($A$2:$A$9=$B$11)*($D$2:$D$9=G2)*$B$2:$B$9*($C$2:$C$9=""),($A$2:$A$9=$B$12)*($D$2:$D$9=G2)*$B$2:$B$9*($C$2:$C$9<>"")))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
test2.xlsx
ABCDEFGH
1DatePriceFri/SunIndentifierToday's DateIndentifierPrice
210/30/2010AAA10/30/20AAA10
310/30/2015BBB10/30/20BBB15
410/30/2020SCCC10/30/20CCC21
510/30/2075SDDD10/30/20DDD76
611/01/2021SCCC
711/01/2076SDDD
811/01/2011AAA
911/01/2016BBB
10
11Today's Date10/30/20New Years Day1/1/2020
12Last BD - 1 or Holiday adjusted11/01/20Martin Luther King, Jr. Day1/20/2020
13Washington's Birthday2/17/2020
14Good Friday4/10/2020
15Memorial Day5/25/2020
16Independence Day7/3/2020
17Labor Day9/7/2020
18Thanksgiving Day11/26/2020
19Christmas Day12/25/2020
Sheet1
Cell Formulas
RangeFormula
H2:H5H2=MAX(IF({1,0},($A$2:$A$9=$B$11)*($D$2:$D$9=G2)*$B$2:$B$9*($C$2:$C$9=""),($A$2:$A$9=$B$12)*($D$2:$D$9=G2)*$B$2:$B$9*($C$2:$C$9<>"")))
Press CTRL+SHIFT+ENTER to enter array formulas.
Thank you i will test it out this week with all the data and see how it works. appreicate it. working on my small sample thus far.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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