Thanks Niek. There is going to have to be some manual preparation around
importing the data upon receipt. I was hoping to stay away from that as it is
for a colleague. In the source data used there are missing cells, and is is
not strictly inalphabetical order. there are also numerous entries that have
the same first words ie

it customer 1
it customer 2
it customer 3

This also seems to be causing problems. May have to go the way of a VBA
search and copy macro.

Thanks for all your helps though

"Niek Otten" wrote:

You can use VLOOKUP instead, with the 4th argument set to FALSE. Look in HELP for details; not that the 3rd argument is the
relative column number, nor the column ID.
Don't use the whole column to search in, just search the filled part of it.
Probably your formula will be something like

=VLOOKUP($A1,Sheet2!$A$1:$B$24,2,FALSE)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"stuartjk" wrote in message ...
| Just finding that out, is there any way I can get the correct results without
| ascending order? possibly not using lookup, is there any thing else?
|
| For what I am trying to do it is essential.
|
| "Niek Otten" wrote:
|
| 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
|
|
|