using vlookup to find a value but use value only if starts with te

[Brian]

Hello,
Here is my request,

sheet 1
sheet 2
------------------------------------------------------------------------------------------------
In Sheet2 cell b2 I want to perform a vlookup that if i could write it in
somewhat plain speech it would say this: In sheet 2 B2:
vlookup(B1,'sheet'1!,A1:C5, this is where if the value is found, i want
column 1 returned but only if the cell's contents starts with text, if it
starts with a number I want the contents of sheet2 cell A1 returned in sheet
2 B2

I hope that makes some sense and your help is appreciated if you could
provide a formula for this.

Thank You,
Brian

 

[Peo Sjoblom]

Maybe something like this

=IF(ISERR(--LEFT(VLOOKUP(B1,Sheet1!$A$1:$C$5,3,0))),VLOOKUP(B1,Sheet1!$A$1:$C$5,3,0),A1)

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Brian]

I tried your suggestion, but sheet 2 b2 only returns with the contents of
sheet 2 a1. Regardless of whether or not the vlookup returned a cell that
started with text or a number. If the vlookup finds a cell that starts with
text, then that text should be returned in sheet2 b2. Otherwise if it starts
with a number then the contents of sheet 2 cell a1 should be returned in
sheet2 b2
For example
sheet 2 b2 wants content from a vlookup
lookup sheet2 A2 "jones"
find jones on Sheet1 in the range A1:C5. it found "jones" in cell C1 now
return the contents of 2 cells to the left of C1 but only if it starts with
text. If the 2nd cell to the left of C1 starts with a number, then return the
value of A1 on Sheet 2.

I apologize if I am not articulating this correctly, hopefully you can sift
through this message and make sense of what I am saying

Thanks again
Brian

 

[Max]

Admit it's quite confusing, Brian <g>
but here's a poke at it ..

Assuming the lookup value is in B1 in Sheet2

Put in B2 of Sheet2:

=IF(ISNUMBER(LEFT(VLOOKUP(B1,Sheet1!$A$1:$C$5,1,0))+0),A1,IF(ISTEXT(LEFT(VLO
OKUP(B1,Sheet1!$A$1:$C$5,1,0))),VLOOKUP(B1,Sheet1!$A$1:$C$5,1,0),""))