VLOOKUP

[Zac]

In COL E of sheet 1 I have the P/N's listed,
and I want to extract the cost from sheet 2, by matching the P/N in Col D, and then pull the Cost which is on the same line, but in Col AA.

Initially I tried a formula, but thought
that VLOOKUP only works with adjacent cells.
So I inserted a Col and now in sheet 2 I have the P/N in AA, and the Cost in AB.

I use the following, but it fails (I get a #N/A result):

=VLOOKUP(E6,Sheet2!$$AA$6:$AB$1875,2,FALSE)
P/N in AA
Cost in AB

What did I do wrong?
Is there an easier way?


HAD A HIDDEN COLUMN. WORKED FINE.



[ This Message was edited by: Zac on 2002-08-08 11:12 ]

[IML]

It is probably an issue with formatting.
If your "matching" cell


use the following formulas on your "matching" numbers on each sheet to see if there is a difference
=l

[lenze]

Check on the formatting of your cells. If you enter a partnumber as a number, but your list is TEXT, you will get an #N/A. I would also suggest you Name your range in stead of using cell references. This is very useful whe working on different sheets. Also, VLOOKUP doesn't care about adjacent columns. That's what the second agrument takes care of.

[IML]

On 2002-08-08 11:07, Zac wrote:
In COL E of sheet 1 I have the P/N's listed,
and I want to extract the cost from sheet 2, by matching the P/N in Col D, and then pull the Cost which is on the same line, but in Col AA.

Initially I tried a formula, but thought
that VLOOKUP only works with adjacent cells.
So I inserted a Col and now in sheet 2 I have the P/N in AA, and the Cost in AB.

I use the following, but it fails (I get a #N/A result):

=VLOOKUP(E6,Sheet2!$$AA$6:$AB$1875,2,FALSE)
P/N in AA
Cost in AB

What did I do wrong?
Is there an easier way?


HAD A HIDDEN COLUMN. WORKED FINE.


[ This Message was edited by: Zac on 2002-08-08 11:12 ]

OOPs.
Let try again.
First, find your "matching cell" on sheet 2. Let say it is sheet2!aa14

type in
=sheet2!aa14=e6

I suspect the answer will be false. If so, see if there are any differences in the following formulas when applied to e6 and sheet2!aa14

=Len(cell)
=isnumber(cell)

[Aladin Akyurek]

In COL E of sheet 1 I have the P/N's listed,
and I want to extract the cost from sheet 2, by matching the P/N in Col D, and then pull the Cost which is on the same line, but in Col AA.

In F6 in Sheet1 enter:

=INDEX(Sheet2!$AA$6:$AA$1875,MATCH(E6,Sheet2!$D$6:$D$1875,0))

should work.


[ This Message was edited by: Aladin Akyurek on 2002-08-08 11:19 ]

[wittmannm]

=VLOOKUP(E6,Sheet2!$$AA$6:$AB$1875,2,FALSE)change to =VLOOKUP(E6,Sheet2!$$AA$6:$AB$1875,28,FALSE) the 28 represents column AB.

[IML]

EDIT - Just noticed the orignal post has been modified - a hidden column was supposedly the cause of his woes.



Wittmanm -
I think the 2 is okay. The third argument is the column of the table, not the column of the spreadshhet. The 28 would probably result in a ref! error since it bigger than the table (xr X 2c).

On 2002-08-08 11:26, wittmannm wrote:
=VLOOKUP(E6,Sheet2!$$AA$6:$AB$1875,2,FALSE)change to =VLOOKUP(E6,Sheet2!$$AA$6:$AB$1875,28,FALSE) the 28 represents column AB.

[ This Message was edited by: IML on 2002-08-08 11:39 ]

[wittmannm]

IML,
I stand corrected, thank you.