VLookup Problem

A

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
 
D

Domenic

ajpowers said:
*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. *
Click to expand...

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
 
A

ajpowers

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

Domenic

ajpowers said:
*New problem, the formula returns a "N/A#" error when the item numbe
has alpha characters in it. *
Click to expand...

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

ajpowers

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

Domenic

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

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
 
A

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
 
D

Domenic

ajpowers said:
*I understand, my name is Amy though.*
Click to expand...

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? *
Click to expand...

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
 
R

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?
 
Top