Finding the closest-lower value in a range of data, using 2 variables

[Vermili0n]

Hello, I am using Excel 2010 and I have this issue:

I have an inventory table where I register the entry of products.
Every time there is an entry of product, I should register its part number, the date of the entry, and the most recent date before the current entry.

Therefore, I am looking for a formula that automatically allows me to identify the closest date before the current entry of data I mentioned; however, it should only look within the dates that correspond to the item in the row (result 1). I also want to know the position of the cell (result 2) and then change the column (result 3)

Something like this:

ITEM DATE OF ENTRY Result I
A January 1, 2001 "FIRST ENTRY"
A January 22, 2001 January 20, 2001
A January 20, 2001 January 1, 2001
B January 4, 2001 "FIRST ENTRY"
B January 5, 2001 January 4, 2001

ITEM DATE OF ENTRY Result II
A January 1, 2001 "FIRST ENTRY"
A January 22, 2001 B3
A January 20, 2001 B1
B January 4, 2001 "FIRST ENTRY"
B January 5, 2001 B4

ITEM DATE OF ENTRY Result III
A January 1, 2001 "FIRST ENTRY"
A January 22, 2001 F3
A January 20, 2001 F1
B January 4, 2001 "FIRST ENTRY"
B January 5, 2001 F4

I think this retrieves the first result I want:

=IF(MAX(IF(IF($A$2:$A$6=A2,$B$2:$B$6)<B2,IF($A$2:$A$6=A2,$B$2:$B$6)))=0,"FIRST ENTRY",MAX(IF(IF($A$2:$A$6=A2,$B$2:$B$6)<B2,IF($A$2:$A$6=A2,$B$2:$B$6))))

CTRL+SHIFT+ENTER

How could I get 2 & 3?

What do you think?
Thank you!

PD. Thanks FDibbins for helping me with the title of the thread.

[FDibbins]

Hi, welcome to the forum

I changed your title for you For future reference, to change a Title on your post, click EDIT POST then Go Advanced and change your title.

[FDibbins]

Not sure if this is what you want - nor where you intend to put it, but see if this helps,,,

To find the latest date for an item (before today)...
=LARGE(IF($A$2:$A$6=A2,$B$2:$B$6,0),1)
To ID the cell ref for that item...
="A"&MATCH(A2&LARGE(IF($A$2:$A$6=A2,$B$2:$B$6,0),1),$A$2:$A$6&$B$2:$B$6,0)+1

Both of these are ARRAY formulas...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Vermili0n]

Not sure if this is what you want - nor where you intend to put it, but see if this helps,,,

To find the latest date for an item (before today)...
=LARGE(IF($A$2:$A$6=A2,$B$2:$B$6,0),1)
To ID the cell ref for that item...
="A"&MATCH(A2&LARGE(IF($A$2:$A$6=A2,$B$2:$B$6,0),1),$A$2:$A$6&$B$2:$B$6,0)+1

Both of these are ARRAY formulas...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Thank you! That's great!

With your insight I could get this:

For result 1:

=IF(MAX(IF(IF($A$2:$A$7=A2,$B$2:$B$7)<B2,IF($A$2:$A$7=A2,$B$2:$B$7)))=0,"FIRST ENTRY",MAX(IF(IF($A$2:$A$7=A2,$B$2:$B$7)<B2,IF($A$2:$A$7=A2,$B$2:$B$7))))

For results 2&3:

=IFERROR("F"&MATCH(A2&C2,$A$2:$A$7&$B$2:$B$7,0)+1,"FIRST ENTRY")

Both of them are arrays (CTRL+SHIFT+ENTER).

I think this covers the whole Issue, I am gonna test this formulas right away!

- Vermilion

[FDibbins]

Look forward to hearing back from you