If you data is exactly as you post, then it is not sorted ascending (which is necessary).
"customer 10" is smaller than "customer 2"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"stuartjk" wrote in message ...
| Please find below a workbook using 2 different sheets but using loopup to
| reference column 1 and return the value of column 2. This is going to be used
| where data order is not always standard so straight value copy is not
| possible.
|
| This formula is being used
|
| =IF((LOOKUP($A1,Sheet2!$A:$A,Sheet2!$B:$B))<"",(L OOKUP($A1,Sheet2!$A:$A,Sheet2!$B:$B)),"")
|
| and should only return a value if column 2 (sheet2) has a value
| corresponding to column 1 (sheet1 and sheet2)
|
| Please view the output enclosed and advise why only customers 1 and 16-24
| are valid. Note there is no 25 and 26 in the source sheet (2)
|
| Source (sheet2)
|
| customer 1 1277
| customer 2 28
| customer 3 511
| customer 4 512
| customer 5 513
| customer 6 514
| customer 7 515
| customer 8 516
| customer 9 517
| customer 10 518
| customer 11 519
| customer 12 520
| customer 13 521
| customer 14 522
| customer 15 523
| customer 16 524
| customer 17 525
| customer 18 526
| customer 19 527
| customer 20 528
| customer 21 529
| customer 22 530
| customer 23 531
| customer 24 532
|
|
| Outcome (sheet1)
|
| customer 1 1277
| customer 2 527
| customer 3 532
| customer 4 532
| customer 5 532
| customer 6 532
| customer 7 532
| customer 8 532
| customer 9 532
| customer 10 1277
| customer 11 1277
| customer 12 1277
| customer 13 1277
| customer 14 1277
| customer 15 1277
| customer 16 524
| customer 17 525
| customer 18 526
| customer 19 527
| customer 20 528
| customer 21 529
| customer 22 530
| customer 23 531
| customer 24 532
| customer 25 532
| customer 26 532
|
| to see for yourself, type in the details in a sheet source(sheet2) and then
| copy the customer names and formula into sheet1.
|
| Thanks