Vlookup

[Pauls123]

Hi all, This is a bit of a tricky one, not really sure how to explain it. I am setting up a rather long and complicated excel 2010 spreadsheet for horse racing. I have a very long column A1 full of horses names and various data next to them in columns B, C, D, E, F and G. All this data appertains to the horse/s last run/s. Columns I and so on contain lookup formulas and other formulas appertaining to the columns I just mentioned.

Now of course horses have more than one recent start and all the data I have is in alphabetically order and also in date order. Hence my VLOOKUP formulas just picks up the first piece of data it comes to for each horse at its latest start, which is ok.

Supposing I want to also include its second last start as well. Can this be done, or is it all getting to complicated.

Regards
Paul

 

[T. Valko]

Hi all, This is a bit of a tricky one, not really sure how to explain it. I am setting up a rather long and complicated excel 2010 spreadsheet for horse racing. I have a very long column A1 full of horses names and various data next to them in columns B, C, D, E, F and G. All this data appertains to the horse/s last run/s. Columns I and so on contain lookup formulas and other formulas appertaining to the columns I just mentioned.

Now of course horses have more than one recent start and all the data I have is in alphabetically order and also in date order. Hence my VLOOKUP formulas just picks up the first piece of data it comes to for each horse at its latest start, which is ok.

Supposing I want to also include its second last start as well. Can this be done, or is it all getting to complicated.

Regards
Paul

If the data is sorted or grouped together then you can use an INDEX/MATCH formula to extract the nth instances of data for a lookup value.

Book1
	A	B	C	D	E	F
2	Horse1	1	_	Horse2	2	40
3	Horse1	23	_	_	_	9
4	Horse1	9	_	_	_	_
5	Horse2	40	_	_	_	_
6	Horse2	9	_	_	_	_
7	Horse3	71	_	_	_	_
8	Horse3	65	_	_	_	_
9	Horse3	23	_	_	_	_
10	Horse3	65	_	_	_	_
Sheet1

We want to lookup info for Horse2 entered in D2.

Enter this formula in E2. This will return the count of records for Horse2.

=COUNTIF(A2:A10,D2)

Enter this formula in F2 and copy down until you get blanks.

=IF(ROWS(F$2:F2)>E$2,"",INDEX(B$2:B$10,MATCH(D$2,A$2:A$10,0)+ROWS(F$2:F2)-1))

 

[Pauls123]

Hi there T Valko. Thanks for your reply, this seems very interesting indeed. I'll try this very shortly today, just tied up out in my shed doing something,....and let you know how it goes,,.......thanks again,

Regards
Paul, down in australia

 

[Pauls123]

Hi again Biff,

I am trying that second formula, but all my cells in column F, are coming up blank. Any reason do you think?

Paul

 

[T. Valko]

Hi again Biff,

I am trying that second formula, but all my cells in column F, are coming up blank. Any reason do you think?

Paul

What does the COUNTIF formula return?

Post the EXACT lookup formula you're using.

 

[John Davis]

Hi all, This is a bit of a tricky one, not really sure how to explain it. I am setting up a rather long and complicated excel 2010 spreadsheet for horse racing. I have a very long column A1 full of horses names and various data next to them in columns B, C, D, E, F and G. All this data appertains to the horse/s last run/s. Columns I and so on contain lookup formulas and other formulas appertaining to the columns I just mentioned.

Now of course horses have more than one recent start and all the data I have is in alphabetically order and also in date order. Hence my VLOOKUP formulas just picks up the first piece of data it comes to for each horse at its latest start, which is ok.

Supposing I want to also include its second last start as well. Can this be done, or is it all getting to complicated.

Regards
Paul

Another option is VLOOKUPNTH by Peter Moran

'This VLOOKUP Function can return numbers 
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.
' 2/1/05 - Modified to handle Wildcards - "=" changed to "like"

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 Like lookup_value Then
      '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

 

[Pauls123]

Hi Biff,

The "countif" formula worked perfectly. I was trying to extract a portion of my spreadsheet to put onto here, but I can only seem to do it as values, not as formulas. Not being very good at all this.

And John, thanks for your reply also, however I dont really know how to do all that, wish I did.

Regards,
Paul

 

[T. Valko]

Hi Biff,

The "countif" formula worked perfectly. I was trying to extract a portion of my spreadsheet to put onto here, but I can only seem to do it as values, not as formulas. Not being very good at all this.

And John, thanks for your reply also, however I dont really know how to do all that, wish I did.

Regards,
Paul

Can you post a sample file so we can see what you're trying to do?

You can use a free file hosting site if need be.