VLOOKUP.. that uses T/F values

RWayneH
RWayneH used Ask the Experts™
on
using a vlookup.  Is there a way to chg the formula to say something like.  If true, put a "Y" and the cell and if false put an "N" in the cell?

=VLOOKUP(LEFT(A5,9),Sheet1!A:B,2,FALSE)

Please advise and thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Your question is not clear.

Do you want to place a Y in the formula cell if LEFT(A5,9) is found in column A of Sheet1 and a N if it is not found?

Would help if you can upload a sample workbook with desired output filled manually.
RWayneH

Author

Commented:
Lets make it easier.
=VLOOKUP(A5),Sheet1!A:B,2,FALSE)  If True = Y and if False = F
RWayneH

Author

Commented:
I have

=IF(ISNA(VLOOKUP(A1,HighlightRangeOrInsertNamedRange,1,FALSE)),"Whatever text you want here",VLOOKUP(A1, HighlightRangeOrInsertNamedRange,1,FALSE))

but it only covers the if N/A or False side,  how do we add the true piece?
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Start Today!
NorieAnalyst Assistant
Commented:
Perhaps.

=IF(ISNUMBER(MATCH(LEFT(A5,9),Sheet1!A:A,0)), "Y", "N")
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
I don't think I get you correctly. Both the Vlookup formulas are same in your IF statement.
ISNA is evaluated to be True if a formula returns #N/A.
So in general the formula should be like this.....

=IF(ISNA(Vlookup_Formula),"N","Y")
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
So your original formula will become like this....

=IF(ISNA(VLOOKUP(LEFT(A5,9),Sheet1!A:B,2,FALSE)),"N","Y")

Open in new window

Top Expert 2015
Commented:
You can do something like this...

=VLOOKUP(LEFT(A5,9),Sheet1!A:B,2,if(b5="Y",0,1))

Open in new window


Assuming you putting value of Y/N in B1 and it will change the vlookup to true or false accordingly..

Saurabh...
RWayneH

Author

Commented:
Thanks for the help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial