Vlookup help. Anyway to replace a specific value with a custom value? turn N into 0 etc.

[JCarp123]

Hey guys first post here, looking for some help with a weird request.

I am trying to do a VLOOKUP to pull data from a column on one sheet to another, (Which I can accomplish), but replace certain found values with a custom term.

So the column is called "Paperless" and is a list of whether or not that person has paperless or not. The options from the source is "N" and "Y" , [obviously for No, and Yes.]

the vlookup I have created so far is this;

=IF(ISNA(VLOOKUP(B2,'[ManageCustomers.xlsx]ManageCustomers (1)'!$A$2:$I$8204,9,FALSE)),"0",VLOOKUP(B2,'[ManageCustomers.xlsx]ManageCustomers (1)'!$A$2:$I$8204,9,FALSE))


So this pulled the column "paperless" from worksheet2 to worksheet1 and made it so if the #N/A value was returned, it showed a 0. The vlookup works.

What I am trying to accomplish is to change the "N's" to "0's" as well, and the "Y's" to return 1.


Right now the vlookup is WORKING , but instead of returning "N" and "Y" I want N to become 0 and Y to become 1 on the worksheet1.

Is this possible? Please help :D thank you.

[protonLeah]

Try this one:

Please Login or Register  to view this content.

[npatridge]

Try this:

=IF(ISNA(VLOOKUP(B2,'[ManageCustomers.xlsx]ManageCustomers (1)'!$A$2:$I$8204,9,FALSE)),"0",IF(VLOOKUP(B2,'[ManageCustomers.xlsx]ManageCustomers (1)'!$A$2:$I$8204,9,FALSE)="Y",1,IF(VLOOKUP(B2,'[ManageCustomers.xlsx]ManageCustomers (1)'!$A$2:$I$8204,9,FALSE)="N",0)))

Rats, was beaten to it.

[TMS]

Are you really using 2007, as your profile indicates? If you are, you can use IFERROR rather than repeating the formula inside IF(ISNA(), ...)

Whatever, if the result can only be "Y" or "N", test the formula in an IF and choose your outcome. =IF(formula="N",0,1)

Regards, TMS

[JCarp123]

Try this one:

Please Login or Register  to view this content.

This worked out great thank you

So the logic of this statement is that if any error shows up, replace it with 0. if Y would be shown, show 1. If anything else would show (thus, 0) then show 0?

thank you!!