VLOOKUP and MID Function

[BLUV]

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?

 

[Sean Timmons]

perhaps 807600 is in text format.

Perhaps

=VLOOKUP("807600",TimeTotal,3,FALSE)

 

[BLUV]

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?

 

[Sean Timmons]

Try this:

=IF(ISERROR(MID($B2,FIND(" -",$B2)-6,6)),"0",VALUE(MID($B2,FIND("
-",$B2)-6,6),0))

 

[BLUV]

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?

 

[Francis]

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

 

[FatBytestard]

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?

 

[BLUV]

Thanks Francis. The formula allowed me save it in the cell as text.

 

[BLUV]

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]

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.