Thanks for you help unfortunately, the issue seems to be with lookup.
Simplyfying the formula to:

=LOOKUP(A:A,Sheet2!A:A,Sheet2!B:B)

returns exactly the same responses. Looking at previous posts this seems to
be an issue with ordering. Both source and lookup fields have to be in
alphabetical/numerical order and with exactly the same entries.

Is there any way I can achieve a correct response ie

customer 10 to return 518 and customer 25 to be blank?

if lookup cannot be used, is there something else.

Thanks

"Dave F" wrote:

Try something like:
=IF(N(ISBLANK((LOOKUP($A1,Sheet2!$A:$A,Sheet2!$B:$ B)),(LOOKUP($A1,Sheet2!$A:$A,Sheet2!$B:$B)),"")

"" is not a numerical value, therefore you can't test for whether the lookup
value is less than or greater than ""

But you can test for whether the lookup value is blank or not (which is what
my modified formula does, above.)

Dave
--
Brevity is the soul of wit.


"stuartjk" wrote:

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