VLOOKUP ERROR

[Lyndon CSWP]

Playing around, in order to keep my mind sharp (sharp as it can get) I created a spreadsheet of Pick 3 lottery numbers and how often they have been drawn over the years. Laugh if you will. I understand they are random. However, I wanted to sort my data in an area that I could look up a 3 digit draw, sort the 6 different combinations of the 3 number and collect the data from the sheet to derive a "score" if you will, of most drawn numbers.
I'm using a VLOOKUP to collect a number from a column to place in the needed cell. I had got it to work but when dragging the cell down to populate the concurring cells, it did the correct thing until it got past a certain cell and failed to provide the data with a #N/A response. I cannot for the life of me figure out what I am doing wrong. I have a test spreadsheet I can supply to everyone. I'm just not sure how to attach it just yet as I finally decided to join this forum.
I'm attaching a screenshot which someone might figure out what I am referring to. If someone needs to see the actual test spreadsheet I would be glad to attach it if they can give me a clue as to how. Be gentle. I'm not likely to know what half of you would be describing as my problem with technical jargon.

 

[BigBeachBananas]

Make sure that columns T and H are the same data type i.e. you're comparing text to text and not text to number.

Try

=T101=H101

See if this returns true.

 

[Lyndon CSWP]

Make sure that columns T and H are the same data type i.e. you're comparing text to text and not text to number.

Try

=T101=H101

See if this returns true.

The H column is all formatted as text and the cells in the T column are derived from =H*** even though they are formatted as General. I tried changing the format in the T column to text and it did not make a difference, returning the same error.

 

[BigBeachBananas]

The H column is all formatted as text and the cells in the T column are derived from =H*** even though they are formatted as General. I tried changing the format in the T column to text and it did not make a difference, returning the same error.

What does it return when you do this?

=T101=H101

 

[Lyndon CSWP]

=T101=H101 returns TRUE
=T102=H102 returns FALSE

 

[BigBeachBananas]

=T101=H101 returns TRUE
=T102=H102 returns FALSE

This suggests that T and H are not the same for all rows.

 

[Lyndon CSWP]

This suggests that T and H are not the same.

Yes, thanks for your help. How can they be the same in 101 but not 102 when they were derived the same way? This is with both cells formatted as text.

 

[BigBeachBananas]

Yes, thanks for your help. How can they be the same in 101 but not 102 when they were derived the same way? This is with both cells formatted as text.

I don't have a good answer to that, especially in Excel 2010. (Someone else might).

Just make sure that T=H returns TRUE then your VLOOK should be working.

 

[Lyndon CSWP]

I don't have a good answer to that, especially in Excel 2010. (Someone else might).

Just make sure that T=H returns TRUE then your VLOOK should be working.

I realized something. I did not derive that cell from =H102. It was derived from =MID($S102,1,1)&MID($S102,2,1)&MID($S102,3,1) as one way to sort it. The S column is =H***.

 

[BigBeachBananas]

I realized something. I did not derive that cell from =H102. It was derived from =MID($S102,1,1)&MID($S102,2,1)&MID($S102,3,1) as one way to sort it. The S column is =H***.

Why don't you use LEFT instead of concat all the MID's

=LEFT($S102,3)