VLOOKUP and MID Function

  • Thread starter Thread starter BLUV
  • Start date Start date
B

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?
 
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?
 
Try this:

=IF(ISERROR(MID($B2,FIND(" -",$B2)-6,6)),"0",VALUE(MID($B2,FIND("
-",$B2)-6,6),0))
 
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?
 
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
 
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.
 
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.
 
Back
Top