vlookup question



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
"=VLOOKUP($E$4:$E$2429,adjustments!$A$7:$B$265,2,FALSE)". however, it
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?




I think you are missing something :)
In the formula
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...

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 -



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():

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


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


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.

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