VLookup Problem

[ajpowers]

How do I get information from a data table to enter automatically?
I have a thousand rows of different items sorted by customer with thei
contract prices, now I need the standard retail price next to eac
unique price.

I've tried a VLOOKUP formula, but I'm obviously doing something wrong

 

[Domenic]

*How do I get information from a data table to enter automatically?
I have a thousand rows of different items sorted by customer wit
their contract prices, now I need the standard retail price next t
each unique price.

I've tried a VLOOKUP formula, but I'm obviously doing somethin
wrong. *

Hi,

You could try something like this:

Sheet2
--------

Service RetailPrice
ServiceA $2,500
ServiceB $2,600

Sheet1
--------

Company Service ContractPrice RetailPrice
ABC ServiceA $2,000 2500
DEF ServiceB $2,200 2600
XYZ ServiceA $2,450 2500

Then, put the following formula in D2 in Sheet1 and copy down:

=VLOOKUP(B2,Sheet2!$A$2:$B$3,2,0)

Hope this helps

 

[ajpowers]

THANK YOU! That was perfect

 

[Domenic]

*THANK YOU! That was perfect! *

You're welcome

 

[ajpowers]

New problem, the formula returns a "N/A#" error when the item number ha
alpha characters in it

 

[Domenic]

*New problem, the formula returns a "N/A#" error when the item numbe
has alpha characters in it. *

Can you post an example of the data you're using

 

[ajpowers]

The formatting isn't transferring correctly and it's hard to read. Ca
I attach a file here

 

[Domenic]

*The formatting isn't transferring correctly and it's hard to read.
Can I attach a file here? *

Sorry John, but I don't particularly like opening files from people
don't know. I'm sure you're a nice guy.

If you're getting a #N/A error, then obviously it hasn't found a match
Make sure that the values for those cells are the same, and that the
don't contain spaces before or after the characters, or a space betwee
the numbers and alpha characters.

Try using a formula to see if one cell matches the other, like,

=B2=A2

If you get false, then that would mean they are not the same and woul
explain the error.

Let us know if that helps

 

[ajpowers]

I understand, my name is Amy though. I've verified that the ite
numbers are there, and that there are no erroneous spaces. So do yo
think it could be that since alpha characters are in the front, and/o
the back that the formula is viewing the data range as out of order

 

[Domenic]

*I understand, my name is Amy though.*

I'm very sorry! I'm not sure why I thought you were John.

*So do you think it could be that since alpha characters are in th
front, and/or the back that the formula is viewing the data range a
out of order? *

It shouldn't make any difference. As long as the value of one is equa
to the value of the other.

Did you use a formula to see if in fact they were equal

 

[RagDyer]

If the lookup value is not *exactly* the same as the data in the lookup
column, you will get an #N/A error!

I don't quite follow just *what* has the "alpha" characters.
Is it the lookup value, or the lookup column in the data list?

If the lookup column in the data list looks something like this,
AB12345AB,
And, your lookup value in B2 is,
12345,
You might adjust your formula to something like this:

=VLOOKUP("*"&B2&"*",Sheet2!$A$2:$B$3,2,0)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



I understand, my name is Amy though. I've verified that the item
numbers are there, and that there are no erroneous spaces. So do you
think it could be that since alpha characters are in the front, and/or
the back that the formula is viewing the data range as out of order?