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

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

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

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

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

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

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?

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

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?

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