|
Vlookup query
Hello Everyone
I have two worksheets if the number in sheet 1 cell B2 is in column B of sheet 2 (say B36) I want the number in A36 to be copied to sheet one. I have written the following but keep getting #N/A - What am I doing wrong please? =IF(COUNTIF(sheet 2!$B$2:$B$576,B2),VLOOKUP(B2,sheet 2!$A$2:$K$511,1,0),0) -- Thanks as always |
Vlookup query
=IF(COUNTIF(sheet2!$B$2:$B$576,B2),VLOOKUP(B2,she et2!$A$2:$K$511,1,0),0)
Your row ranges aren't the same size, 576 versus 511 ? Try this... =IF(COUNTIF(Sheet2!B$2:B$511,B2),INDEX(Sheet2!A$2: A$511,MATCH(B2,Sheet2!B$2:B$511,0)),0) -- Biff Microsoft Excel MVP "Lise" wrote in message ... Hello Everyone I have two worksheets if the number in sheet 1 cell B2 is in column B of sheet 2 (say B36) I want the number in A36 to be copied to sheet one. I have written the following but keep getting #N/A - What am I doing wrong please? =IF(COUNTIF(sheet 2!$B$2:$B$576,B2),VLOOKUP(B2,sheet 2!$A$2:$K$511,1,0),0) -- Thanks as always |
Vlookup query
Fabulous as always Biff - thanks so much.
-- Lise "T. Valko" wrote: =IF(COUNTIF(sheet2!$B$2:$B$576,B2),VLOOKUP(B2,she et2!$A$2:$K$511,1,0),0) Your row ranges aren't the same size, 576 versus 511 ? Try this... =IF(COUNTIF(Sheet2!B$2:B$511,B2),INDEX(Sheet2!A$2: A$511,MATCH(B2,Sheet2!B$2:B$511,0)),0) -- Biff Microsoft Excel MVP "Lise" wrote in message ... Hello Everyone I have two worksheets if the number in sheet 1 cell B2 is in column B of sheet 2 (say B36) I want the number in A36 to be copied to sheet one. I have written the following but keep getting #N/A - What am I doing wrong please? =IF(COUNTIF(sheet 2!$B$2:$B$576,B2),VLOOKUP(B2,sheet 2!$A$2:$K$511,1,0),0) -- Thanks as always . |
Vlookup query
Sorry Biff I do have one more question - The below works perfectly - however
I have now realised that there may be multiple matches, is there a way to show all of these say with a comma between or am I asking for too much?? -- Thanks as always Lise "Lise" wrote: Fabulous as always Biff - thanks so much. -- Lise "T. Valko" wrote: =IF(COUNTIF(sheet2!$B$2:$B$576,B2),VLOOKUP(B2,she et2!$A$2:$K$511,1,0),0) Your row ranges aren't the same size, 576 versus 511 ? Try this... =IF(COUNTIF(Sheet2!B$2:B$511,B2),INDEX(Sheet2!A$2: A$511,MATCH(B2,Sheet2!B$2:B$511,0)),0) -- Biff Microsoft Excel MVP "Lise" wrote in message ... Hello Everyone I have two worksheets if the number in sheet 1 cell B2 is in column B of sheet 2 (say B36) I want the number in A36 to be copied to sheet one. I have written the following but keep getting #N/A - What am I doing wrong please? =IF(COUNTIF(sheet 2!$B$2:$B$576,B2),VLOOKUP(B2,sheet 2!$A$2:$K$511,1,0),0) -- Thanks as always . |
Vlookup query
is there a way to show all of these say
with a comma between To do it that way would require a VBA UDF. How about returning each result to its own cell? That's fairly simple. For example: Horizontally: ....A1........B1........C1 result1...result2...result3 Vertically: A1 = result1 A2 = result2 A3 = result3 -- Biff Microsoft Excel MVP "Lise" wrote in message ... Sorry Biff I do have one more question - The below works perfectly - however I have now realised that there may be multiple matches, is there a way to show all of these say with a comma between or am I asking for too much?? -- Thanks as always Lise "Lise" wrote: Fabulous as always Biff - thanks so much. -- Lise "T. Valko" wrote: =IF(COUNTIF(sheet2!$B$2:$B$576,B2),VLOOKUP(B2,she et2!$A$2:$K$511,1,0),0) Your row ranges aren't the same size, 576 versus 511 ? Try this... =IF(COUNTIF(Sheet2!B$2:B$511,B2),INDEX(Sheet2!A$2: A$511,MATCH(B2,Sheet2!B$2:B$511,0)),0) -- Biff Microsoft Excel MVP "Lise" wrote in message ... Hello Everyone I have two worksheets if the number in sheet 1 cell B2 is in column B of sheet 2 (say B36) I want the number in A36 to be copied to sheet one. I have written the following but keep getting #N/A - What am I doing wrong please? =IF(COUNTIF(sheet 2!$B$2:$B$576,B2),VLOOKUP(B2,sheet 2!$A$2:$K$511,1,0),0) -- Thanks as always . |
Vlookup query
Hi Biff - sorry your #1 Pain again!!
all is working well (+ I've made some changes based on other tips from you on previous notes) however there are still issues with duplicates. Currently I have as an example =IF(COUNTIF(Tasks!B$2:B$576,A59),INDEX(Tasks!A$2:A $576,MATCH(A59,Tasks!B$2:B$576,0)),"-") On the tasks sheet row 59,60 and 61 column B are all the same which is the same as the number listed on the current sheet in A59, A60 & A61 - but the data I'm collecting from the tasks sheet in columns D, F & J (same rows) change on each row which the formula isnt picking up The formula gives the correct answer for A59 but when I drag down to use for A60 and A61 it only provides the row 59 answers again. Gosh I hope this makes it clearer Biff its the only way I can think to clarify for you. Any assistance as always appreciated Lise "T. Valko" wrote: is there a way to show all of these say with a comma between To do it that way would require a VBA UDF. How about returning each result to its own cell? That's fairly simple. For example: Horizontally: ....A1........B1........C1 result1...result2...result3 Vertically: A1 = result1 A2 = result2 A3 = result3 -- Biff Microsoft Excel MVP "Lise" wrote in message ... Sorry Biff I do have one more question - The below works perfectly - however I have now realised that there may be multiple matches, is there a way to show all of these say with a comma between or am I asking for too much?? -- Thanks as always Lise "Lise" wrote: Fabulous as always Biff - thanks so much. -- Lise "T. Valko" wrote: =IF(COUNTIF(sheet2!$B$2:$B$576,B2),VLOOKUP(B2,she et2!$A$2:$K$511,1,0),0) Your row ranges aren't the same size, 576 versus 511 ? Try this... =IF(COUNTIF(Sheet2!B$2:B$511,B2),INDEX(Sheet2!A$2: A$511,MATCH(B2,Sheet2!B$2:B$511,0)),0) -- Biff Microsoft Excel MVP "Lise" wrote in message ... Hello Everyone I have two worksheets if the number in sheet 1 cell B2 is in column B of sheet 2 (say B36) I want the number in A36 to be copied to sheet one. I have written the following but keep getting #N/A - What am I doing wrong please? =IF(COUNTIF(sheet 2!$B$2:$B$576,B2),VLOOKUP(B2,sheet 2!$A$2:$K$511,1,0),0) -- Thanks as always . . |
| All times are GMT +1. The time now is 02:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com