need help with Excel VLOOKUP

Microsoft Microsoft excel 2007 full vers...
December 31, 2009 at 07:42:04
Specs: Windows Vista
my name is Mila , I’m trying to do this: I have a list of golf courses {Sheet!B3:B100} with different rates {Sheet!C3:H101} so on the SHEET 2 I have a SURCH columns where if I tape the name of golf course it will give me all the rates (I used =VLOOKUP)
Now THE PROBLEMs ARE 1): if I make mistake in the name it gives me totally different rates!!!! :( What can I do to make sure it give’s expected numbers?
2): and if in SURCH Sheet, course name blank, all the columns have #N/A?????

PS: here is some pictures, if it can be helpful:

If someone has time to help me I would really appreciate!!! Thank you so much!!!!

Configuration: Windows Vista Internet Explorer 8.0

See More: need help with Excel VLOOKUP

Report •

December 31, 2009 at 08:24:14

When you use VLOOKUP() do you include the Optional range_lookup (TRUE or FALSE)

Something like this:

If you omit the FALSE, VLOOKUP will return the value from another row if there isn't an exact match, (I won't go into the exact logic - but you can read it in the Excel Help files for VLOOKUP).

As to the NA see this response here.


Report •

December 31, 2009 at 08:29:01
In reverse order...

2 - You can eliminate the #N/A by wrapping an IF function around your VLOOKUP as follows:


If A1 is empty, the formula will return nothing otherwise it will execute the VLOOKUP.

1 - re: if I make mistake in the name it gives me totally different rates!

Please be more specific. What types of "mistake" are you talking about? Obviously Excel can't know that you meant to type in Crow Creek if you type in Crow Park.

You could use a Data validation list made up of the names that are in the table so that you wouldn't have to actually type the name. However, if you choose the wrong name from the list, even Excel can't "fix" that - only you can, by choosing a different name.

Report •

December 31, 2009 at 08:57:12

To help avoid mistakes when entering names, use the list of names in B3 to B100 as a validation list.

If you put the name to be found into cell A1, select Cell A1
From the Ribbon select Data and then Data Validation.
In the dialog box that opens, in the Settings Tab, choose 'List' from the Allow: drop-down
In the Source: box put the range of cells containing the names B3:B100.
Click OK
Now when you enter a name in Cell A1 it won't let you put a misspelled name in the cell !

There is one limitation - the List of names must be on the same worksheet as the cell unless you use a named range.

You can name a range of cells on another sheet and use the name in the Source: box e.g. =CourseNames

Select B3:B100, then from the Ribbon select Formulas - Define Name and give the list a name (no spaces allowed).
Click OK


Report •

Related Solutions

December 31, 2009 at 11:02:45
WOW !!! thankyou SSOOO much !!! i couldn't find this information anywere, ya life savers!!!! thank you for taking your time to work on my question and explain It To Me with such detailed information about each port!!! thank you! AND HAPPY NEW YEAR!!!!!!!!!

Report •

December 31, 2009 at 11:04:47
And Special thank you to , Humar ! you r really good!!!

Report •

December 31, 2009 at 12:14:14
You're most Welcome.

Happy New Year to you.


Report •

Ask Question