# Formula needed to extract a value based off multiple criteria

#### ItalianPlatinum

##### Active Member
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

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

Replies
2
Views
139
Replies
0
Views
50
Replies
3
Views
132
Replies
5
Views
536
Replies
2
Views
513

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.

### Which adblocker are you using?

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

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