vlookup based on 2 criteria (text and most recent date)

[jargno]

Hi! My name's John. I'm using Excel 2007 and I've got a question about concatenated cells and vlookup that I've been stuck on for a few hours - I would really appreciate any help you can provide.

I've included a picture so you can see what I'm working on. Basically I'd like J1:J6 to auto-populate with the most recent product, but for the life of me I can't figure out how to include the latest date (associated with the container) in the formula. Is there another function, or an easier way to do this?

<img src="http://i.imgur.com/IdLIw.png" alt="" title="Hosted by imgur.com" />

 

[jeffreybrown]

Hi John,

Try and replace A2 with =B2&"|"&C2

It worked for me.


A2=B2&"|"&C2
<TABLE style="WIDTH: 209pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=278><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3136" width=98><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1632" width=51><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2400" width=75><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1728" width=54><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21 width=98>Shelf 1|40687</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=51>Shelf 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=75 align=right>5/24/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=54>Helmet</TD></TR></TBODY></TABLE>


J2=VLOOKUP(I2&"|"&Q1,A:D,4,0)
<TABLE style="WIDTH: 79pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=105><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1632" width=51><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1728" width=54><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 38pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=51>Shelf 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=54>Helmet</TD></TR></TBODY></TABLE>

Edit: BTW, next time it would help to paste data instead of a picture because with a picture we cannot cut & paste for testing. Look at my signature block for an HTML maker.

 

[T. Valko]

Hi! My name's John. I'm using Excel 2007 and I've got a question about concatenated cells and vlookup that I've been stuck on for a few hours - I would really appreciate any help you can provide.

I've included a picture so you can see what I'm working on. Basically I'd like J1:J6 to auto-populate with the most recent product, but for the life of me I can't figure out how to include the latest date (associated with the container) in the formula. Is there another function, or an easier way to do this?

Here's an alternative which eliminates the need for a column of concatenated values.

Array entered**:

=INDEX(D$2:D$22,MATCH(1,IF(B$2:B$22=I$2,IF(C$2:C$22=Q$2,1)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[jargno]

Thanks for getting back to me Jeff! I already have that in cells A2, so it didn't really change things.

"&Q2" in the formula bar needs to be replaced with something that says 'the most recent date next to Shelf 1', but I don't really know how. Once again I'm very much appreciative of any help.

 

[jargno]

Here's an alternative which eliminates the need for a column of concatenated values.

Array entered**:

=INDEX(D$2:D$22,MATCH(1,IF(B$2:B$22=I$2,IF(C$2:C$22=Q$2,1)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Thanks Biff, that's a good idea and I feel like it's moving me in the right direction. I think that formula is still dependent on today() being in C:C though, so every new day I'll lose the past data..

 

[T. Valko]

"&Q2" in the formula bar needs to be replaced with something that says 'the most recent date next to Shelf 1', but I don't really know how. Once again I'm very much appreciative of any help.

Thanks Biff, that's a good idea and I feel like it's moving me in the right direction. I think that formula is still dependent on today() being in C:C though, so every new day I'll lose the past data..

OK, if the date needs to be the most recent date that corresponds to the container...

Still array entered**:

=INDEX(D$2:D$22,MATCH(1,IF(B$2:B$22=I$2,IF(C$2:C$22=MAX(IF(B$2:B$22=I$2,C$2:C$22)),1)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[jargno]

Biff- THANK YOU!! I've been trying to figure this out for so long, and I'll have to go over the formula later under a microscope, to fully understand it. I can't extend my gratitude to you enough sir.

 

[T. Valko]

Biff- THANK YOU!! I've been trying to figure this out for so long, and I'll have to go over the formula later under a microscope, to fully understand it. I can't extend my gratitude to you enough sir.

You're welcome. Thanks for the feedback!

 

[joelexcel]

OK, if the date needs to be the most recent date that corresponds to the container...

Still array entered**:

=INDEX(D$2:D$22,MATCH(1,IF(B$2:B$22=I$2,IF(C$2:C$22=MAX(IF(B$2:B$22=I$2,C$2:C$22)),1)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Hi Biff -

Hoping you're still around.

This works almost perfectly for my purpose as well. Is there any way to, using this Shelf, Seats and Handlebars scenario as example again - if for a given shelf, there, not all dates are filled in, would like this to result in an error, or a "----?----" - is there something extra I can easily add to this formula?

Cheers
-Joel