# Lookup Problem

#### Franz

##### Board Regular
=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

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Assuming the data you supplied is in columns starting with A, what is the expected answer you want to see?

pll

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!

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),"-")

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

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

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?

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?

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
ABCDEFG
1
27298307-002360
37298425C8366-0
47299025D11012-0
50-
6
772983UNI10/25/025507-002360A120195
872984BRW11/15/02250025C8366-012TBN
972985BRW11/8/021500D2022451TBN
1072986BRW12/5/021000D3007702TBN
1172987BRW11/22/021000D3022268TBN
1272988BRW12/5/023000D2025711TBN
1372989BRW12/5/022000D2025932TBN
1472990BRW2/5/0220025D11012-02TBN
1572991PEP10/25/02412291-001036333
Sheet4
</SPAN>

I hope this helps.

Regards!

Yogi

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!

Replies
5
Views
203
Replies
2
Views
443
Replies
12
Views
328
Replies
4
Views
104
Replies
0
Views
136

1,203,244
Messages
6,054,363
Members
444,718
Latest member
r0nster

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back