3 inputs, single output, unable to use SUMPRODUCT to return correct value

[JackDanIce]

Hi,

Have a table with Date, Category, Source and Rate, need value returned from Rate column, inputs are date, category type and source and each Date&Category&Source is unique (want to avoid using a helper column and VLOOKUP)

If inputs are:

Date: 30/06/2018
Source: Data1
Category: Number

I need to find the row where date is closest >= match in column (e.g. date column has 01/06/2018 or earlier, 30/06/2018 needs to match against this row number (where source = Data1 and category = Number)

I currently have

=SUMPRODUCT(--($Q$3:$Q$100=F3),--($P$3:$P$100=G3),--(D3>=$O$3:$O$100),$R$3:$R$100)

To remove rows of non-interest, however, part in red is date compare I'm having issues with (I think) in returning correct rate.

Can anyone suggest correction please?

Thank you,
Jack

 

[James006]

Hi Jack,

Regarding the red part to find the nearest date, you could test the following:

MATCH(MIN(ABS(O3:O100-$D$3)), ABS(O3:O100-$D$3), 0)

Hope this will help

 

[JackDanIce]

That looks promising, thank you James006, will update after tested.

 

[James006]

That looks promising, thank you James006, will update after tested.

You are welcome ... :wink:

Hope this will help

 

[JackDanIce]

Morning James,

Substituting your suggestion for part in red only returns #N/A
Entering it by inself into a different cell as a normal formula returns #N/A
Entering it by itself into a different cell as an array formula, returns 3 which is the correct row match number and hence date

I think there's a problem co-ering or creating an array of 0s and 1s with your suggestion that fits the rest of the sumproduct?
Checked all ranges sizes are equal, any ideas?

 

[JackDanIce]

Just solved(?)

For the lookup table (P:R), I added a Key column being P&Q&R and then using VLOOKUP with 4th argument TRUE, it appears to return the correct values as input arguments change. Final formula:

=VLOOKUP(E3&B3&G3,$O$3:$S$10,5,1)

Lot simpler than initial approach though intent was avoid using a Key (helper) column.. needs must however!

Thanks for looking into it James.

 

[James006]

Glad you could solve your problem ..:wink:

Take Care

 

[JackDanIce]

Urgh, it doesn't appear to be solved.. found values being returned that shouldn't have been. Back to testing, thank you anyway!

 

[Peter_SSs]

Just solved(?)

For the lookup table (P:R), I added a Key column being P&Q&R and then using VLOOKUP with 4th argument TRUE, it appears to return the correct values as input arguments change. Final formula:

=VLOOKUP(E3&B3&G3,$O$3:$S$10,5,1)

Lot simpler than initial approach though intent was avoid using a Key (helper) column.. needs must however!

That certainly does not sound like it should work robustly to me, though I am having difficulty determining exactly what you have in what columns cells.
Could we have those 10 rows of sample data & details of what is in the other cells (which seem to have changed from post 1) & the expected result?
(With nearly 9,000 posts & often helping others, I thought you would know to give sample data & expected results )

 

[JackDanIce]

Hi Peter, my bad I thought the explanation was sufficient and work laptop do not have a decent way to post screenshots, but right to call me up on that!

May have also been impaitent, posted new question here which contains example layout - best I could with straight forward text input: [h=3]Index + Match, returning incorrect value, date match required so please close or delete this thread or that if appropriate.[/h]
I haven't read @Aladin Akuyrek's reply yet but will do shortly

Table looks like:

Date Source Rate
01/01/1990 Property 0.20
01/01/1990 Shares 0.23
06/04/2016 Property 0.24
06/04/2016 Shares 0.25
(colour to align columns and data)

In input is 01/03/2018, source is Property I need to return 0.24
For same date, if source is Shares then return 0.25

Both times because 06/04/2016 is the nearest date before input date (so a date pre 2016 would use 1990's rates for same sources)

I think I read @Colin Legg's blog ages ago something about using TRUE in VLOOKUP for dates. The table will always be in chronological order and then alphabetical by source (only the date part must stay ordered, everything else can change relatively)

Hope this adds better colour to the query!