Formula needed to extract a value based off multiple criteria

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
400
Office Version
  1. 2016
  2. 2010
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

hnsd24_CN

Board Regular
Joined
Oct 13, 2020
Messages
78
Office Version
  1. 2016
Platform
  1. Windows
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.
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
400
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,939
Messages
5,621,716
Members
415,853
Latest member
Newlife72

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
Top