|
VLOOKUP and MID Function
I have 2 worksheets. This first worksheet I am using a formula to strip out
6 numerics from a cell of mixed alpha numeric characters. Here is the formula I am using successfully. IF(ISERROR(MID($B2,FIND(" -",$B2)-6,6)),"0",MID($B2,FIND(" -",$B2)-6,6)) Worksheet 1 A B C 2 807600 A807600 - Apps Platform Facility 272.2 Now, on the second sheet I would like to perform a vlookup function similar to the one below. Where 'TimeTotal's is a reference to cells = 'Worksheet 1'!A2-C400 VLOOKUP(807600,TimeTotal,3,FALSE) The current way I have this returns '#NA' in the VLOOKUP, but I can see 807600 listed on the other sheet. Can you help me get this to work? -- RyGuy |
VLOOKUP and MID Function
perhaps 807600 is in text format.
Perhaps =VLOOKUP("807600",TimeTotal,3,FALSE) "BLUV" wrote: I have 2 worksheets. This first worksheet I am using a formula to strip out 6 numerics from a cell of mixed alpha numeric characters. Here is the formula I am using successfully. IF(ISERROR(MID($B2,FIND(" -",$B2)-6,6)),"0",MID($B2,FIND(" -",$B2)-6,6)) Worksheet 1 A B C 2 807600 A807600 - Apps Platform Facility 272.2 Now, on the second sheet I would like to perform a vlookup function similar to the one below. Where 'TimeTotal's is a reference to cells = 'Worksheet 1'!A2-C400 VLOOKUP(807600,TimeTotal,3,FALSE) The current way I have this returns '#NA' in the VLOOKUP, but I can see 807600 listed on the other sheet. Can you help me get this to work? -- RyGuy |
VLOOKUP and MID Function
It probably is but I don't know what I need to do? I've tried right clicking
on both cells in both worksheets and selected format "text", so they are the same, but I still get a #NA. I then tried switch all the cells to "number" format with not luck, and then again with "General" format. Do I need to alter my MID function so that when it strips out the numbers they get placed in the cell in a different manner? Then my VLOOK function would see the number instead of the function? -- RyGuy "Sean Timmons" wrote: perhaps 807600 is in text format. Perhaps =VLOOKUP("807600",TimeTotal,3,FALSE) "BLUV" wrote: I have 2 worksheets. This first worksheet I am using a formula to strip out 6 numerics from a cell of mixed alpha numeric characters. Here is the formula I am using successfully. IF(ISERROR(MID($B2,FIND(" -",$B2)-6,6)),"0",MID($B2,FIND(" -",$B2)-6,6)) Worksheet 1 A B C 2 807600 A807600 - Apps Platform Facility 272.2 Now, on the second sheet I would like to perform a vlookup function similar to the one below. Where 'TimeTotal's is a reference to cells = 'Worksheet 1'!A2-C400 VLOOKUP(807600,TimeTotal,3,FALSE) The current way I have this returns '#NA' in the VLOOKUP, but I can see 807600 listed on the other sheet. Can you help me get this to work? -- RyGuy |
VLOOKUP and MID Function
Try this:
=IF(ISERROR(MID($B2,FIND(" -",$B2)-6,6)),"0",VALUE(MID($B2,FIND(" -",$B2)-6,6),0)) "BLUV" wrote: It probably is but I don't know what I need to do? I've tried right clicking on both cells in both worksheets and selected format "text", so they are the same, but I still get a #NA. I then tried switch all the cells to "number" format with not luck, and then again with "General" format. Do I need to alter my MID function so that when it strips out the numbers they get placed in the cell in a different manner? Then my VLOOK function would see the number instead of the function? -- RyGuy "Sean Timmons" wrote: perhaps 807600 is in text format. Perhaps =VLOOKUP("807600",TimeTotal,3,FALSE) "BLUV" wrote: I have 2 worksheets. This first worksheet I am using a formula to strip out 6 numerics from a cell of mixed alpha numeric characters. Here is the formula I am using successfully. IF(ISERROR(MID($B2,FIND(" -",$B2)-6,6)),"0",MID($B2,FIND(" -",$B2)-6,6)) Worksheet 1 A B C 2 807600 A807600 - Apps Platform Facility 272.2 Now, on the second sheet I would like to perform a vlookup function similar to the one below. Where 'TimeTotal's is a reference to cells = 'Worksheet 1'!A2-C400 VLOOKUP(807600,TimeTotal,3,FALSE) The current way I have this returns '#NA' in the VLOOKUP, but I can see 807600 listed on the other sheet. Can you help me get this to work? -- RyGuy |
VLOOKUP and MID Function
Thank Sean, but the Value function didn't work. It didn't like the $B2 I was
passing it and I think it wanted text in quotes. I did try spelling out 807066 and placing it in quotes and was able to get the variable 272.75, which is exactly what I was looking for. But need to be able to find this using a refence to the cell (B2) instead of having to manually type in the number with quotes. Any suggestions there? -- BLuv "Sean Timmons" wrote: Try this: =IF(ISERROR(MID($B2,FIND(" -",$B2)-6,6)),"0",VALUE(MID($B2,FIND(" -",$B2)-6,6),0)) "BLUV" wrote: It probably is but I don't know what I need to do? I've tried right clicking on both cells in both worksheets and selected format "text", so they are the same, but I still get a #NA. I then tried switch all the cells to "number" format with not luck, and then again with "General" format. Do I need to alter my MID function so that when it strips out the numbers they get placed in the cell in a different manner? Then my VLOOK function would see the number instead of the function? -- RyGuy "Sean Timmons" wrote: perhaps 807600 is in text format. Perhaps =VLOOKUP("807600",TimeTotal,3,FALSE) "BLUV" wrote: I have 2 worksheets. This first worksheet I am using a formula to strip out 6 numerics from a cell of mixed alpha numeric characters. Here is the formula I am using successfully. IF(ISERROR(MID($B2,FIND(" -",$B2)-6,6)),"0",MID($B2,FIND(" -",$B2)-6,6)) Worksheet 1 A B C 2 807600 A807600 - Apps Platform Facility 272.2 Now, on the second sheet I would like to perform a vlookup function similar to the one below. Where 'TimeTotal's is a reference to cells = 'Worksheet 1'!A2-C400 VLOOKUP(807600,TimeTotal,3,FALSE) The current way I have this returns '#NA' in the VLOOKUP, but I can see 807600 listed on the other sheet. Can you help me get this to work? -- RyGuy |
VLOOKUP and MID Function
try this
=VLOOKUP(TEXT(A1,"0"),Sheet1!A2:C10,3,0) change the cell's references to yours -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "BLUV" wrote: Thank Sean, but the Value function didn't work. It didn't like the $B2 I was passing it and I think it wanted text in quotes. I did try spelling out 807066 and placing it in quotes and was able to get the variable 272.75, which is exactly what I was looking for. But need to be able to find this using a refence to the cell (B2) instead of having to manually type in the number with quotes. Any suggestions there? -- BLuv "Sean Timmons" wrote: Try this: =IF(ISERROR(MID($B2,FIND(" -",$B2)-6,6)),"0",VALUE(MID($B2,FIND(" -",$B2)-6,6),0)) "BLUV" wrote: It probably is but I don't know what I need to do? I've tried right clicking on both cells in both worksheets and selected format "text", so they are the same, but I still get a #NA. I then tried switch all the cells to "number" format with not luck, and then again with "General" format. Do I need to alter my MID function so that when it strips out the numbers they get placed in the cell in a different manner? Then my VLOOK function would see the number instead of the function? -- RyGuy "Sean Timmons" wrote: perhaps 807600 is in text format. Perhaps =VLOOKUP("807600",TimeTotal,3,FALSE) "BLUV" wrote: I have 2 worksheets. This first worksheet I am using a formula to strip out 6 numerics from a cell of mixed alpha numeric characters. Here is the formula I am using successfully. IF(ISERROR(MID($B2,FIND(" -",$B2)-6,6)),"0",MID($B2,FIND(" -",$B2)-6,6)) Worksheet 1 A B C 2 807600 A807600 - Apps Platform Facility 272.2 Now, on the second sheet I would like to perform a vlookup function similar to the one below. Where 'TimeTotal's is a reference to cells = 'Worksheet 1'!A2-C400 VLOOKUP(807600,TimeTotal,3,FALSE) The current way I have this returns '#NA' in the VLOOKUP, but I can see 807600 listed on the other sheet. Can you help me get this to work? -- RyGuy |
VLOOKUP and MID Function
On Fri, 28 Aug 2009 10:58:01 -0700, BLUV
wrote: It probably is but I don't know what I need to do? If you look at his response to you, he placed you lookup inside quotation marks. Did you try that? |
VLOOKUP and MID Function
Thanks Francis. The formula allowed me save it in the cell as text.
-- RyGuy "Francis" wrote: try this =VLOOKUP(TEXT(A1,"0"),Sheet1!A2:C10,3,0) change the cell's references to yours -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "BLUV" wrote: Thank Sean, but the Value function didn't work. It didn't like the $B2 I was passing it and I think it wanted text in quotes. I did try spelling out 807066 and placing it in quotes and was able to get the variable 272.75, which is exactly what I was looking for. But need to be able to find this using a refence to the cell (B2) instead of having to manually type in the number with quotes. Any suggestions there? -- BLuv "Sean Timmons" wrote: Try this: =IF(ISERROR(MID($B2,FIND(" -",$B2)-6,6)),"0",VALUE(MID($B2,FIND(" -",$B2)-6,6),0)) "BLUV" wrote: It probably is but I don't know what I need to do? I've tried right clicking on both cells in both worksheets and selected format "text", so they are the same, but I still get a #NA. I then tried switch all the cells to "number" format with not luck, and then again with "General" format. Do I need to alter my MID function so that when it strips out the numbers they get placed in the cell in a different manner? Then my VLOOK function would see the number instead of the function? -- RyGuy "Sean Timmons" wrote: perhaps 807600 is in text format. Perhaps =VLOOKUP("807600",TimeTotal,3,FALSE) "BLUV" wrote: I have 2 worksheets. This first worksheet I am using a formula to strip out 6 numerics from a cell of mixed alpha numeric characters. Here is the formula I am using successfully. IF(ISERROR(MID($B2,FIND(" -",$B2)-6,6)),"0",MID($B2,FIND(" -",$B2)-6,6)) Worksheet 1 A B C 2 807600 A807600 - Apps Platform Facility 272.2 Now, on the second sheet I would like to perform a vlookup function similar to the one below. Where 'TimeTotal's is a reference to cells = 'Worksheet 1'!A2-C400 VLOOKUP(807600,TimeTotal,3,FALSE) The current way I have this returns '#NA' in the VLOOKUP, but I can see 807600 listed on the other sheet. Can you help me get this to work? -- RyGuy |
VLOOKUP and MID Function
I did try in in quotes, but couldn't get it to work. However, I though I
would try it again to see if I missed anything and I found something very interesting. I had to change the format of my cell(s) to General first, and then type in the VALUE function with the quotes. Then it worked fine. When I had the cell format as Text it would not work. Weird and painful...but I'm on my way now. Thank you for your time and help. -- Bluv "FatBytestard" wrote: On Fri, 28 Aug 2009 10:58:01 -0700, BLUV wrote: It probably is but I don't know what I need to do? If you look at his response to you, he placed you lookup inside quotation marks. Did you try that? |
VLOOKUP and MID Function
Sean DUDE!!! Hey I'm excited to report that I finally get it to work. What
I discovered is that I had to change my cell format to General and then use your formula to change the data. When I had the format set to number or text, the formula would not work for me. Weird and painful....but I'm on my way now and have learned a lot. Thank you very much for your time and effort. Really appreciate it. -- Bluv "Sean Timmons" wrote: Try this: =IF(ISERROR(MID($B2,FIND(" -",$B2)-6,6)),"0",VALUE(MID($B2,FIND(" -",$B2)-6,6),0)) "BLUV" wrote: It probably is but I don't know what I need to do? I've tried right clicking on both cells in both worksheets and selected format "text", so they are the same, but I still get a #NA. I then tried switch all the cells to "number" format with not luck, and then again with "General" format. Do I need to alter my MID function so that when it strips out the numbers they get placed in the cell in a different manner? Then my VLOOK function would see the number instead of the function? -- RyGuy "Sean Timmons" wrote: perhaps 807600 is in text format. Perhaps =VLOOKUP("807600",TimeTotal,3,FALSE) "BLUV" wrote: I have 2 worksheets. This first worksheet I am using a formula to strip out 6 numerics from a cell of mixed alpha numeric characters. Here is the formula I am using successfully. IF(ISERROR(MID($B2,FIND(" -",$B2)-6,6)),"0",MID($B2,FIND(" -",$B2)-6,6)) Worksheet 1 A B C 2 807600 A807600 - Apps Platform Facility 272.2 Now, on the second sheet I would like to perform a vlookup function similar to the one below. Where 'TimeTotal's is a reference to cells = 'Worksheet 1'!A2-C400 VLOOKUP(807600,TimeTotal,3,FALSE) The current way I have this returns '#NA' in the VLOOKUP, but I can see 807600 listed on the other sheet. Can you help me get this to work? -- RyGuy |
| All times are GMT +1. The time now is 07:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com