CODEWITHSUNDEEP
excel-formula
Abel
Abel

Reputation: 21

lookup values between date range

Good day, I'm trying to use V&hLookup to get my values but I'm not sure if I'm using the correct formula.

I'm working of 2 sets of data (sheet1 and sheet2). Sheet1 the data is vertical (e.g. unique identifiers and dates) and sheet2 the dates are horizonal and values below. Both sheets have unique identifiers Vertically. Question: On sheet1, I need to look up the values from sheet2, if there is not values then the formula must look at the next available value after the start date? I got the values as examples on sheet1 as to what it should be

E.g.

Sheet1

Key Dates Values
12345 01/01/2020 120.00
678910 01/02/2020 150.00
789456 01/03/2020 110.00

Sheet2

Key 01/01/2020 01/02/2020 03/04/2020
12345 120.00 0.00 150.00
678910 0.00 120.50 130.00
789456 160.00 120.00 110.00

Thanks a million.

Upvotes: 2

Views: 464

Answers (1)

P.b
P.b

Reputation: 11415

Edited answer: suggested solution using MATCH(search_value,match_range,1) in combination with INDEX gave wrong results.

The following should do:

=INDEX(Sheet2!$A$1:$D$4,            
       MATCH(A5,Sheet2!$A$1:$A$4,0), 
       MATCH( 
             MIN(IF(Sheet2!$B$1:$D$1>=B5,Sheet2!$B$1:$D$1,"")),
             Sheet2!$A$1:$D$1,
             0))

Enter with ctrl+shift+enter in older Excel versions.

The row is found matching the Key-value and the column is found by matching the column where the date value in Sheet2 headers greater than or equal to search date value.

enter image description here

And when using Tables:

=INDEX(Table3,
       MATCH([@Key],Table3[Key],0),
       MATCH(
             MIN(IFERROR(IF(--Table3[#Headers]>=[@Dates],--Table3[#Headers]),9^9)),
             --Table3[#Headers],
             0))

Where I needed to workaround the date headers becoming text instead of number values by adding leading -- to the value/range.

enter image description here

Upvotes: 2

Related Questions