Lookup and reference for category and date

[Krokes]

Hy

I'm having serious difficulties with getting together a formula for a little bit complicated lookup.

I have 2 tables. In the first table I have categories in the first column (e.g. red, blue, green), in the second column I have dates (DD.MM.YYYY). And in the third column I would like to get the values from the second table.

In the second table I also have categories in the first column, dates in the second column und values in the third.

I would now like to get the values from the second table that coincide with the categories. And in addition the dates should also be equal to the date in the first table OR the date from the second table should be as close as possible but BEFORE the date in the first table.


Example
Table1
Red 07.08.2005 ???


Table 2
Red 18.04.2005 three
Blue 11.06.2005 one
Red 06.08.2005 four


In this example the value that should be looked up is "four".

I hope anyone can help me! Thank you!!

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Saarang84]

Try,

Please Login or Register  to view this content.

This as an array formula, confirmed by Ctrl+Shift+Enter. Input the formula in cell C2, press Ctrl+Shift+Enter and copy down. Is this what you need ?

[Krokes]

Thank you Saarang84 for the formula. Is it possible that there is a small mistake in it? It doesn't work in my sheet. Ctrl+Shift+Enter doesn't help unfortunatly.

[samba_ravi]

=INDEX('Table 2'!$C$2:$C$5,MATCH(MAX(INDEX(('Table 2'!$A$2:$A$5=$A2)*(9^9+1/(B2+1-'Table 2'!$B$2:$B$5)),0)),INDEX(('Table 2'!$A$2:$A$5=$A2)*(9^9+1/(B2+1-'Table 2'!$B$2:$B$5)),0),0))
Try this formula and copy towards down

[Krokes]

Wow this really seems to solve my problem. Thank you very much. I would love to understand this a little better, could you quickly explain what this formula does? Will it work on a table with a e.g. 60'000 lines?

[samba_ravi]

=INDEX('Table 2'!$C$2:$C$5,MATCH(MAX(INDEX(('Table 2'!$A$2:$A$5=$A2)*(1/(B2+1-'Table 2'!$B$2:$B$5)),0)),INDEX(('Table 2'!$A$2:$A$5=$A2)*(1/(B2+1-'Table 2'!$B$2:$B$5)),0),0))
This is also will work

[Krokes]

OK thank you! One thing doesnt work. If I change 'Table 2'!B4 from "17.12.2004" into "18.12.2004", I get #DIV/0! in 'Table 1'!C6. It should actually change the value to "26"

[Krokes]

And also when I remove all "Blue" lines from Table 2, Table 1 just gets values from category "Red" which is wrong. If there is no corresponding category or date that corresponds, I need an emtpy cell, an error would also be ok. Do you know a solution to that problem?

Again my problem:
I need values from the same category (colour) and from the date, that matches the date or from a date that is before the date in Table 1. The category condition alway has to hold. If there is either no category or no date that fits the condition, the cell should be empty or show an error.

[Krokes]

I have not found the mistake. But When I apply it, I get "28" in Table 1!C4 instead of "25"

Try,

Please Login or Register  to view this content.

This as an array formula, confirmed by Ctrl+Shift+Enter. Input the formula in cell C2, press Ctrl+Shift+Enter and copy down. Is this what you need ?

I attached another file to make it a bit clearer...

[samba_ravi]

=IF(SUM(INDEX((('Table 2'!$A$2:$A$8=$A2)*('Table 2'!$B$2:$B$8<=$B2))*1,0))=0,0,INDEX('Table 2'!$C$2:$C$8,MATCH(SMALL(INDEX(('Table 2'!$A$2:$A$8=$A2)*('Table 2'!$B$2:$B$8)*1,0),SUM(INDEX((('Table 2'!$A$2:$A$8=$A2)*('Table 2'!$B$2:$B$8)<=$B2)*1,0))),INDEX(('Table 2'!$A$2:$A$8=$A2)*('Table 2'!$B$2:$B$8)*1,0),0)))
try this

[Krokes]

Oh my god this work absolutly fantastically!! Thank you so much nflsales!! I will try it on my reallife worksheet and will give feedback again!

[Krokes]

It works very well! Thank you very much!!!