[slurpee55]

LOL - it may just have been that you hit 10 posts that caused it to become enabled....

[slurpee55]

It always helps to actually post the design of the real worksheet, not just a concept...if you had, you would have had your answer yesterday.
Here you go, with the data in Column G.
The one error I found was that Ehrenberg had a space preceding or following it, so I copied it over from Sheet2.
You might want to consider using a drop-down list on Sheet1 so you don't get errors of that sort.

[Tonservices]

I am completely 100% sorry. This is 100% my actual current spreadsheet with minor changes. How do I handle locations with spaces in there names? - Take a look at me spreadsheet. Is there a way without a drop box? Or, is the only way with a drop box? Once again!! I'm sorry!!!!

[slurpee55]

No need to be sorry - just letting you know that you will get faster answers if you ask directly for what you need.
As for what to do in your workbook, the formula works as it was in the last file I uploaded.
See attached.

[Tonservices]

Please take a look at my spreadsheet and correct the problem. Thanks

[slurpee55]

I have never used data validation for lists...but I am sure someone here knows how....

[Tonservices]

What if we scratch the column G. What if we moved it to column L. I will use the equals sign to bring it back to column G. Is the spacing a problem for the locations? Can you change the formula to column L instead of G. Thanks again for your help.

[slurpee55]

You can just copy the formula (copy and paste it using the formula bar, don't copy and paste from the cell, because then Excel will change the columns it looks at) from column G over to L without changing it, since it is comparing things in two different columns altogether.

[Nic Cunliffe]

Hi,

A vlookup() should do this for you.

Pick the next empty cell to the right of Ogden, Utah in sheet 1 and enter the formula:

eg. =vlookup(a2,sheet2!;a1:g600,1,false).

In words it is trying to say...
lookup name "ogden, utah, in sheet 2, range a1 to whatever, if you find an exact match, bring back the value in 2nd col of the range.

Using the function wizard will help you to get the structure correct. The 3 digit number must also be to the right of the place name for this to work.

[slurpee55]

Nic, a VLOOKUP won't work because the data it is searching is not sorted...one of the problems of VLOOKUP.
That is why I used Index/Match - the formula is
=IF(ISNA(INDEX(Sheet2!A:B,MATCH(I2,Sheet2!B:B,0),1)),"MISSING",INDEX(Sheet2 !A:B,MATCH(I2,Sheet2!B:B,0),1))

But if you can help with devising a drop-down list, that would be great.

[Tonservices]

If I had all the sites in alphabetical order would the VLOOKUP command work?

[slurpee55]

No, you still get a #N/A error...but don't worry about that - the file I uploaded uses a more complex formula but it works great.
This part
IF(ISNA(INDEX(Sheet2!A:B,MATCH(I2,Sheet2!B:B,0),1)),"MISSING"
merely checks to see if the response will be #N/A (when it MATCH-es the data in I2 with the data in Sheet2 column B) and if it will be, it gives the response of Missing.
The second part
INDEX(Sheet2 !A:B,MATCH(I2,Sheet2!B:B,0),1))
- which only comes into play after it checks to see if it will return a #N/A and the answer is no - then displays the match.

[Tonservices]

Is there a way to add a sheet 2 column B is equal to column A? This VLOOKUP pulls up the site name but not the site number from sheet 2.

***VLOOKUP(I34,Sheet2!B3:B602,1,FALSE)LOOKUP(I34,Sheet2!B3:B602,1,FALSE)** Note: I have also put all my site names in alphabetical order. I think this made a difference.

[Tonservices]

Slurpee55.
Can

[Tonservices]

Slurpee,
I am having troubles with this formula. Can you show me a demostration with my last attachment with the formula working. This attachment has travel plaza for each location. I understand now your comment about N/A for the VLOOKUP. I could only hoped this formula would be the solution. Thanks.