vlookup question

[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
"=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?

 

[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

 

[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.