Using vlookup

P

Patrick C. Simonds

I am not understanding how to use vlookup.

On my Master Worksheet cell B11 contains a number. I would like a formula
which will go to worksheet Accrual and find the number in column A and
then return the value in column B.

Nothing I have tried has worked. I am pretty persistent and will keep
plugging away, but if someone could provide a helping hand, it would be
appreciated.
 
N

Niek Otten

Hi Patrick,

Look at this tutorial:

http://www.contextures.com/xlFunctions02.html

If you still have problems, look here:

=========================================================
Vlookup gives wrong answer

Niek Otten, April 1 2006

Frequent causes:

1. Some cells look like numbers, but are actually text. You can check with the ISTEXT function.
Check both the search arguments and the lookup table.
Formatting as numbers afterwards doesn't help.
Remedy:
Format an empty cell as Number. Enter the number 1. Edit>Copy. Select your "numbers". Edit>Paste Special, check Multiply.

2. The data is not sorted ascending and the 4th argument of the VLOOKUP is TRUE or is omitted.

3. There are spaces or other invisible characters in either the search arguments or the lookup table.
This often happens when you import data from other applications.
Use the LEN() function to see how many characters there really are in the cell and compare that with what you see.
Use the TRIM function to remove all spaces except single spaces between words.
Use the CLEAN function to remove all nonprintable characters. HTML characters can be removed with a macro by David
McRitchie,
which can be downloaded here: http://www.mvps.org/dmcritchie/excel/join.htm#trimall

4. The formula was copied from somewhere else, but the addresses of the lookup table were not absolute so have changed in
the Paste process
and now point to the wrong range.
Use absolute addresses for the lookup table, like $A$1:$B$20 instead of relative addresses like A1:B20.
When editing or entering a formula, use the F4 key to toggle between several forms of relative addresses and absolute ones
(normally only for the table, not for the search argument, but this depends on your specific problem).
The first hit of F4 changes the default relative address to an absolute one. That is usually what you need.
Even better: use a Defined Name for the table instead of cell addresses; Insert>Name>Define.

5. The table was extended after its initial use, but the definition of the table in the VLOOKUP or in the Defined Name was
not adjusted accordingly.
To prevent this from happening: always use explicit bottom and top rows (with dummy error values if necessary)
and insert new rows or cells between those two.
Then the definition of the range or the Defined Name will adjust automatically.
Users of Excel 2003 and higher may profit from the List feature in the Data menu to maintain tables.
=========================================================

If that doesn't solve it either, post again in this thread, and do supply your formaulas, inpt values, expected results and
what you got instead

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I am not understanding how to use vlookup.
|
| On my Master Worksheet cell B11 contains a number. I would like a formula
| which will go to worksheet Accrual and find the number in column A and
| then return the value in column B.
|
| Nothing I have tried has worked. I am pretty persistent and will keep
| plugging away, but if someone could provide a helping hand, it would be
| appreciated.
|
 
P

Patrick C. Simonds

Thanks for your reply and the point to the tutorial. The problem was that I
had not included all the column in the formula. I used Accrual!$A$2:$A$51.
Once I included the second column of the table everything was good.


=VLOOKUP(B13,Accrual!$A$2:$B$51,2,FALSE)
 
S

scott

if you are always looking at B11 try this otherwise adjust the formula
for what you are looking for.
=VLOOKUP(B11,Accrual!A1:B1000,2,False)

If the Accrual worksheet is in anothe rworkbook this would need
adjusting as well. to

=VLOOKUP(B11,[OtherWorkbook]Accrual!A1:B1000,2,False)

ScottD
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vlookup from right to left 2
VLOOKUP formula? 12
Vlookup or similar 7
VLOOKUP forumla for Excel HELP! 1
Excel Excel Vlookup formula based on cell data 7
help with a vlookup 2
Excel IF statement with vlookup 2
Can't Explain N/A in Vlookup 2

Top