Lookup Problem

[Franz]

=IF($C$2=0,"-",LOOKUP($C$2,'J:OrderEntryLog[Data.xls]Sheet1'!$A$1:$G$5000,'J:OrderEntryLog[Data.xls]Sheet1'!$E$1:$E$5000))

Can anyone tell me what I am missing? The results in column A are in ascending numerical order and still get wrong results.

72983 UNI 10/25/02 55 07-002360 A 120195
72984 BRW 11/15/02 2500 25C8366-0 12 TBN
72985 BRW 11/8/02 1500 D202245 01 TBN
72986 BRW 12/5/02 1000 D300770 02 TBN
72987 BRW 11/22/02 1000 D302226 08 TBN
72988 BRW 12/5/02 3000 D202571 01 TBN
72989 BRW 12/5/02 2000 D202593 02 TBN
72990 BRW 2/5/02 200 25D11012-0 02 TBN
72991 PEP 10/25/02 4 12291-001 0 36333
72992 PEP 10/25/02 2 12291-002 0 36333
72993 PEP 10/25/02 2 12291-003 0 36333
72994 PEP 10/25/02 2 12291-004 0 36333
72995 PEP 10/25/02 1 12291-005 0 36333
72996 PEP 10/25/02 1 12291-006 0 36333
72997 PEP 10/25/02 2 12291-007 0 36333
72998 PEP 10/25/02 2 12291-008 0 36333
72999 PEP 10/25/02 1 12291-009 0 36333
73000 PEP 10/25/02 1 12291-010 0 36333
73001 PEP 10/25/02 1 12291-011 0 36333
73002 PEP 10/25/02 1 12291-012 0 36333
73003 PEP 10/25/02 5 12291-013 0 36333
73004 PEP 10/25/02 1 12291-014 0 36333
73005 PEP 10/25/02 1 12291-015 0 36333
73006 PEP 10/25/02 1 12292-001 0 36333
73007 PEP 10/25/02 2 12292-002 0 36333
73008 PEP 10/25/02 2 12292-003 0 36333
73009 PEP 10/25/02 1 12292-004 0 36333
73010 PEP 10/25/02 4 12292-005 0 36333
73011 PEP 10/25/02 2 12292-006 0 36333
73012 PEP 10/25/02 1 12292-007 0 36333
73013 PEP 10/25/02 2 12292-008 0 36333
73014 PEP 10/25/02 2 12292-009 0 36333
73015 PEP 10/25/02 1 12292-010 0 36333
73016 NUM RTV 1 102714 N P70514

 

[plettieri]

Assuming the data you supplied is in columns starting with A, what is the expected answer you want to see?

pll

 

[Franz]

Sorry for not giving more detail.

If I type 72983 in cell C2 ,the result should return 07-002360 in the cell that contains the lookup formula. This is what is happening ,however when I type 72984 in cell C2 I should get 25C8366-0 which is not happening. So on and so forth with each Cell A1 Thru A5000 number entered should return a result from corresponding Cell numbers E1 Thru E5000. I hope this is not to confusing.
Thanks!

 

[Aladin Akyurek]

Use

=IF(C2,VLOOKUP(C2,'J:OrderEntryLog[Data.xls]Sheet1'!$A$1:$G$5000,5,0),"-")

If column A is sorted in ascending order, use:

=IF(C2,VLOOKUP(C2,'J:OrderEntryLog[Data.xls]Sheet1'!$A$1:$G$5000,5),"-")

 

[Franz]

Aladin,

Tried the second formula you sent but still can only get the one result (07-002360).

By the way.I have two linked files in this spreadsheet, data.xls and another.

My understanding was that if the data in Column A is in ascending order then the lookup formula should work for any entry i.e. B1:B5000 , C1:C5000 within the range A1:G5000

 

[Aladin Akyurek]

On 2002-10-11 15:50, Franz wrote:
Aladin,

Tried the second formula you sent but still can only get the one result (07-002360).

By the way.I have two linked files in this spreadsheet, data.xls and another.

My understanding was that if the data in Column A is in ascending order then the lookup formula should work for any entry i.e. B1:B5000 , C1:C5000 within the range A1:G5000

Is that a wrong result? And how many results do you expect to get and from where?

 

[Franz]

Listen to this .

I pasted the data from data.xls into my other linked file and the lookup worked fine . Can anyone tell me why? Can you have more than 1 outside link in a worksheet?

 

[Yogi Anand]

Hi Franz:

As you said, the LOOKUP formula is working fine -- see my worksheet simulation.

Now, we have three entities at work here,

1. the lookup value in C2;
2. the entries in column A thar are coming from your linked source
3. the values in column E that are coming from your linked source

You may now want to troubleshoot by looking at each of these entities has the right value (including the check that these do not have any errant/special/blank characters in them).

Book2
	A	B	C	D	E	F	G
1
2			72983		07-002360
3			72984		25C8366-0
4			72990		25D11012-0
5			0		-
6
7	72983	UNI	10/25/02	55	07-002360	A	120195
8	72984	BRW	11/15/02	2500	25C8366-0	12	TBN
9	72985	BRW	11/8/02	1500	D202245	1	TBN
10	72986	BRW	12/5/02	1000	D300770	2	TBN
11	72987	BRW	11/22/02	1000	D302226	8	TBN
12	72988	BRW	12/5/02	3000	D202571	1	TBN
13	72989	BRW	12/5/02	2000	D202593	2	TBN
14	72990	BRW	2/5/02	200	25D11012-0	2	TBN
15	72991	PEP	10/25/02	4	12291-001	0	36333
Sheet4

</SPAN>

I hope this helps.

Regards!

Yogi

 

[Franz]

Thanks Yogi,

There is nothing wrong with the formula,that much I know.The best guess I have at this point is the fact that I copied the formula from another cell with a lookup formula from the other outside link file which works well . I used the paste special choice with formulas checked which seems to have copied specific attributes from the other linked file.When I get a chance I will try the formula again in a virgin cell.Sometimes these things are not worth the time and trouble to figure out!