Need to improve a formula

[Guest]

Hello,
I hope this request is not too wacky.
This f returns the contents of E20 (which in this case is "1750 - 0410")if
the look up finds a cell that starts with a number but if the look up finds a
cell that starts with a letter then it returns the content of the found cell.
Now I want to add to this, something that states if the lookup finds a cell
that has "Swch" in it then return the contents of that cell along with the
contents of E20

=IF(ISNUMBER(LEFT(VLOOKUP(E27,'[D Patrol Log Even.xls]Patrol
Log'!$C$22:$M$45,11,0))+0),$E$20,IF(ISTEXT(LEFT(VLOOKUP(E27,'[D Patrol Log
Even.xls]Patrol Log'!$C$22:$M$45,11,0))),VLOOKUP(E27,'[D Patrol Log
Even.xls]Patrol Log'!$C$22:$M$45,11,0),""))

Thank You
Brian

 

[Frank Kabel]

Hi
try:
=IF(ISNUMBER(LEFT(VLOOKUP(E27,'[D Patrol Log Even.xls]Patrol
Log'!$C$22:$M$45,11,0))+0),$E$20,IF(ISTEXT(LEFT(VLOOKUP(E27,'[D Patrol
Log
Even.xls]Patrol Log'!$C$22:$M$45,11,0))),VLOOKUP(E27,'[D Patrol Log
Even.xls]Patrol Log'!$C$22:$M$45,11,0) & "-" & $E$20,""))

You may also consider using a two cell approach as you have to
calculate the VLOOKUP function up to three times. So maybe put in your
first cell (e.g. X1):
X1:
VLOOKUP(E27,'[D Patrol Log Even.xls]Patrol Log'!$C$22:$M$45,11,0)
(you can hide this column if you want afterwards)

And in your target cell then use:
=IF(ISNUMBER(-LEFT(X1)),$E$20,IF(ISTEXT(LEFT(X1)),X1 & "-" & $E$20,""))

 

[Guest]

Thank You,
I would try it that way but I already have so many tables and other
reference cells, it looks like a jungle. I am afraid to add another thing for
fear of ruining the whole workbook.

Thanks again,
Brian

Hi
try:
=IF(ISNUMBER(LEFT(VLOOKUP(E27,'[D Patrol Log Even.xls]Patrol
Log'!$C$22:$M$45,11,0))+0),$E$20,IF(ISTEXT(LEFT(VLOOKUP(E27,'[D Patrol
Log
Even.xls]Patrol Log'!$C$22:$M$45,11,0))),VLOOKUP(E27,'[D Patrol Log
Even.xls]Patrol Log'!$C$22:$M$45,11,0) & "-" & $E$20,""))

You may also consider using a two cell approach as you have to
calculate the VLOOKUP function up to three times. So maybe put in your
first cell (e.g. X1):
X1:
VLOOKUP(E27,'[D Patrol Log Even.xls]Patrol Log'!$C$22:$M$45,11,0)
(you can hide this column if you want afterwards)

And in your target cell then use:
=IF(ISNUMBER(-LEFT(X1)),$E$20,IF(ISTEXT(LEFT(X1)),X1 & "-" & $E$20,""))

--
Regards
Frank Kabel
Frankfurt, Germany

Hello,
I hope this request is not too wacky.
This f returns the contents of E20 (which in this case is "1750 - 0410")if
the look up finds a cell that starts with a number but if the look up finds a
cell that starts with a letter then it returns the content of the found cell.
Now I want to add to this, something that states if the lookup finds a cell
that has "Swch" in it then return the contents of that cell along with the
contents of E20

=IF(ISNUMBER(LEFT(VLOOKUP(E27,'[D Patrol Log Even.xls]Patrol
Log'!$C$22:$M$45,11,0))+0),$E$20,IF(ISTEXT(LEFT(VLOOKUP(E27,'[D Patrol Log
Even.xls]Patrol Log'!$C$22:$M$45,11,0))),VLOOKUP(E27,'[D Patrol Log
Even.xls]Patrol Log'!$C$22:$M$45,11,0),""))

Thank You
Brian