vlookup and two tables

[MrByte]

Table one holds City, County, Desc, Code and District Office. Table two has many columns but the one column I am VLOOKUPing is R_City. Which of course holds the city. The formula I have is =VLOOKUP([[#ThisRow],[R_City]],TableOne,5).

Table One:
A R_City B County C Desc D Code E DO
Jacksonville Duval City 1003 Jacksonville
Miami Miami-Dade City 1010 Miami

For some reason it is pulling the DO from the line above. So if I am looking up Miami (the city) and I want the District Office (DO) Miami. But for some reason it is pulling Jacksonville as the DO.

Now I have sorted some of the records but I also I have put them back into the original order. Nothing seems to work correctly.

 

[VoG]

Try

=VLOOKUP(searchvalue,tablerange,columnindex,FALSE)

 

[MrByte]

That returned #N/A.

 

[MrByte]

I found a fix but I don't like it. The fix is this I make the column where I enter the city have Data Validation from the table where I am looking up the search value (City). Which means two things 1) I have to move the data validation table one (city, count, desc, code, DO) to the same worksheet as the table two (my winners table). 2) I have to go through all the entries I have already done and "validate" them.

On number one maybe I am just being picky. On number 2 I have almost 500 entries that I would have to through and validate.

Any other ideas?

 

[Matty]

I am not sure why you are having the issues you describe.

Could you share a small example of your data in a format that can be quickly copied by potential helpers? Easiest way is just to ensure that there are borders around your Cells, select them, hit copy and then paste the data into your message. Please also state the expected result.

Matty