| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup returning incorrect cell values
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
lookup returning incorrect cell values
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
lookup returning incorrect cell values
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
lookup returning incorrect cell values
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
lookup returning incorrect cell values
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
lookup returning incorrect cell values
=IF(VLOOKUP(A1,Sheet2!A:B,2,0)=0,"",VLOOKUP(A1,She et2!A:B,2,0))
"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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
lookup returning incorrect cell values
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 | | | |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
lookup returning incorrect cell values
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 | | | |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
lookup returning incorrect cell values
Actually tried the vlookup with the false in below worked excellently.
thanks. There will still need to be some manual work pre import but at least this cuts it down tremendously. "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 | | | |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return Matched Numeric Values across Rows | Excel Worksheet Functions | |||
| Returning multiple corresponding values using lookup in a list | Excel Discussion (Misc queries) | |||
| LOOKUP returning value in cell above what I was searching for | Excel Discussion (Misc queries) | |||
| Returning all values from a lookup - not just the first/last one | Excel Worksheet Functions | |||
| up to 7 functions? | Excel Worksheet Functions | |||
Linear Mode
