Vlookup formula to return a value only if the cell starts with "F"

[marilynfebruary]

Hi,
I need a vlook up formula to return the value in the last column of spreadsheet when the reference cell starts with a "F". My reference cells are alpha numeric... e.g. F1074 and S1355. So I only want a value for cells starting with F otherwise the cells starting with S should return a "1"

 

[VoG]

Maybe like this

=IF(LEFT(A1,1)<>"F",1,VLOOKUP(A1,B1:C100,2,FALSE))

 

[marilynfebruary]

Hi, thanks for your super quick response... but its not working.
I'm doing the vlookup from another spreadsheet... so I want the following answer:
<TABLE style="WIDTH: 245pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=326><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20 width=64></TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" width=70></TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" width=64></TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20>Code</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff">descr</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff"></TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65>size</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20>F1045</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff">red tabs</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff"></TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65>25</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20>F1325</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff">blue tabs</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff"></TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65>35</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20>S0256</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; mso-ignore: colspan" colSpan=2>bulk tabs 1</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65>30</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20>S4125</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; mso-ignore: colspan" colSpan=2>bulk tabs 2</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65>45</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20></TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff"></TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff"></TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65></TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20>Answer:</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; mso-ignore: colspan" colSpan=4>only the F codes to return an answer</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20>F1045</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff">red tabs</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff"></TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65>25</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20>F1325</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff">blue tabs</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff"></TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65>35</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20>S0256</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; mso-ignore: colspan" colSpan=2>bulk tabs 1</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65>1</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20>S4125</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff; mso-ignore: colspan" colSpan=2>bulk tabs 2</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65>1</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff"></TD></TR></TBODY></TABLE>

 

[VoG]

Try like this

Excel Workbook
	A	B	C
1	Code
2	F1045	red tabs	25
3	F1325	blue tabs	35
4	S0256	1	1
5	S4125	1	1
Sheet8

 

[marilynfebruary]

It looks ike this should work, but it doesn't
If u can't spot an obvious mistake with my formula, then I'll do it the long way by adding another column and inserting the answers for F items only - then do the vlookup from there
=IF(LEFT(A83,1)<>"F",1,VLOOKUP($A83,'LEGACY 2 B PRESS (2)'!B:AX,49,FALSE))

 

[VoG]

Sorry, I can't see anything wrong with your formula and, as you can see, a similar formula worked for me.

Out of interest what does

=LEFT(A83,1)

return. It should be F of course but maybe there's an invisible character to the left of the F.

 

[ncalenti]

I would quickly throw a trim in there just to make sure the problem isn't leading or trailing whitespaces:

=IF(LEFT(trim(A83),1)<>"F",1,VLOOKUP($A83,'LEGACY 2 B PRESS (2)'!B:AX,49,FALSE))

If that doesn't fix it, you may also try not having your array be the entire worksheet. What I mean is that "LEGACY 2 B PRESS (2)'!B:AX" may be better written as LEGACY 2 B PRESS (2)'!B1:AX100 with that 100 being the last row of data on your spreadsheet.

 

[marilynfebruary]

Thank you both.. the formula works!