Finding 2nd value with Vlookup

[gsfmike]

Scenario of data:

12345 6/01/04 ... ...
12345 7/02/04 ... ...
23456 7/15/04 ... ...
32345 6/01/04 ... ...
32345 7/02/04 ... ...
66456 5/12/04 ... ...

Is it possible to have vlookup get a 'HIT' and then use a second value to return the correct entry? 12345 is the first value, however I want to return data with the 7/02/04 date witch is the second value.

I am struggling with any way to accomplish this in Excel. Any thoughts would be appreciated.

 

[bluedragon2]

what type of data are you looking up?

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

If it is a number or date, you can use:

=SUMPRODUCT((A1:A1000=12345)*(B1:B1000=DATEVALUE("7/2/04"))*(C1:C1000))





[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[gsfmike]

Bluedragon, Thanks for such a quick response.

The lookup colum is a series of product numbers and the second colum holds an effective date colum 3 is a price for the date

I tried to do a second lookup, however I did not have a clue how to structure an 'if' statement.

 

[bluedragon2]

try the above formula and adjust the columns for your case.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[gsfmike]

Bluedragon, I am using vlookup(x,range,x,x) to return the price that I wanted. I will play around with the SUMPRODUCT - which I'm not at all familiar with and get back to you.

Thanks again

 

[jfhewitt]

You might want to check out pivot tables

 

[mbarron]

check this thread thread68-839537

Mike

 

[Zathras]

If you don't mind using VBA, take a look at my VLOOKUPNEXT
function in thread68-693835.

I just tried it again, and it seems to work for your situation just the way you want it to.

 

[PeterMoran]

Hi Zathras,

Just wanted to say how useful I have found your VLOOKUPNEXT - it is quite a while since I initially saw it - many thanks!

I also found that by modifying it slightly I could locate any value - I called this VLOOKUPNTH as below:

Function VLOOKUPNTH(lookup_value, table_array As Range, _
col_index_num As Integer, nth_value)
' Extension to VLOOKUP function. Allows for finding
' the "nth" item that matches the lookup value.
Dim nRow As Long
Dim nVal As Integer
Dim bFound As Boolean
VLOOKUPNTH = "Not Found"
With table_array
For nRow = 1 To .Rows.Count
If .Cells(nRow, 1).Value = lookup_value Then
nVal = nVal + 1
End If
If nVal = nth_value Then
VLOOKUPNTH = .Cells(nRow, col_index_num).Text
Exit Function
End If
Next nRow
End With
End Function

Peter Moran

 

[gsfmike]

Thanks to all. I have started the exercise with VBA. It seems that I have quite a bit to learn. Looking past the 'columns and rows' is quite interesting and powerful. Your assistance has helped me solve my initial issue. Again many thanks.

 

[MJPPaba]

I may be misunderstanding your question but:

=INDEX(A1:B6,MATCH(12345,A1:A6,0)+COUNTIF(A1:A6,12345)-1,2)
Result '7/02/04'

Replace the value 12345 in the [match] and [countif] formulae with your desired value or cell reference.

The above assumes you want the value from column 2 pertaining to the most recent entry in column 1. It also assumes that there may be occasions where there can be more than 2 entries.

If you only want the second entry every time then this will need to be amended.

Cheers

Matthew

Who remembers the Natwest Piggies?

 

[Zathras]

Peter, I actually like your version better. It solves the nagging problem that exists with mine (that I was simply ignoring) where the retrieved values are not unique, and so the search never gets past the duplicate entry.

Thanks for posting.