# vlookup question

A

#### aimlessmn

i have three tabs (x2, invoices and adjustments). on each tab is a list of
invoice numbers as well as an amount for payments applied to each invoice. i
need to find the corresponding amounts from the last two worksheets and list
those by the amounts on the first worksheet. i finally found a formula that
works "=VLOOKUP(\$E\$4:\$E\$2429,invoices!\$A\$8:\$B\$2114,2,TRUE)" and
doesn't work unless i actually type in the invoice numbers in the first
worksheet. am i missing something here or do i actually have to go in and
type in all 2,425 invoice numbers in order for the formulas to work properly?

S

#### Sheeloo

I think you are missing something
In the formula
=VLOOKUP(\$E\$4:\$E\$2429,invoices!\$A\$8:\$B\$2114,2,TRUE)
instead of \$E\$4:\$E\$2429 you should use the cell which contains the invoice
number in Sheet X2... first paratemeter is supposed to be a cell not a range
Also change True to False as the fourth parameter...

=VLOOKUP(A1,invoices!\$A\$8:\$B\$2114,2,FALSE)
The way VLOOKUP (example above) works is as follows;
Take the value in E... go to the range invoices!\$A\$8:\$A\$2114 and try to find
a match... if the match is found then move right to the column number 2
(which will be Col B in this case and get the value from there as the result
of the formula.
VLOOKUP returns #N/A if no match is found...

To understand VLOOKUP properly look in HELP or visit Debra's excellent site -
http://www.contextures.com/xlFunctions02.html

D

#### Dave Peterson

Values in cells that contain only digits can be numbers or text. And =vlookup()
will not match a number (123) with text (="123").

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

If the table contains text and your key (first parm in the =vlookup() formula is
numeric, you can use something like:

=vlookup(e1&"",invoices!a:b,2,false)

If the values in the table are numbers and your key is numeric, you can use:

=vlookup(--e1,invoices!a:b,2,false)

But the real problem is when you have a mixture of both in each sheet. I think
it's a much better idea to cleanse the data and not fiddle with the formula.