Vlookup doesn't work - even with TRIM function

[piper_co]

Hi

I am having problems troubleshooting why vlookup doesn't work. Went through the forum and saw a thread that advised to use trim function to eliminate the spaces. Did that. But it works for some cells and doesn't work for some cells. Pls help.

I have attached the file. Column D in tab "decision making" is showing #NA for some cells.

Appreciate all help.

Thanks

[dilipandey]

Hi Piper_co,

Welcome to the forum.

solution... where you have #N/A, dont use trim there

Please Login or Register  to view this content.

see attached:- EXCEL ASSIGNMENT - CABLE TV V2.xlsx

Regards,
DILIPandey
<click on below * if this helps>

[benishiryo]

hi piper_co, welcome to the forum. working in Starhub eh? a better way is to clean up your data & make sure both sheets are the same. the reason those cells dont work is because there are spaces in the "Add-Ons Channel" sheet. so another way to standardise is to trim both sides by using an array formula:
=IF(C31="Yes",VLOOKUP(TRIM(B31),TRIM(Add_Ons_Channel_Listing),2,FALSE),0)+0
press CTRL + SHIFT + ENTER to confirm instead of just ENTER>

[piper_co]

Hi Piper_co,

Welcome to the forum.

solution... where you have #N/A, dont use trim there

Please Login or Register  to view this content.

see attached:- Attachment 204751

Regards,
DILIPandey
<click on below * if this helps>

Hi DILIPandey

It doesn't work. I tried inputting the formula you mentioned, it doesn't seemed to work. I opened your attached amended file, it shows me #name in column D.

Pls advise

Thanks

[piper_co]

hi piper_co, welcome to the forum. working in Starhub eh? a better way is to clean up your data & make sure both sheets are the same. the reason those cells dont work is because there are spaces in the "Add-Ons Channel" sheet. so another way to standardise is to trim both sides by using an array formula:
=IF(C31="Yes",VLOOKUP(TRIM(B31),TRIM(Add_Ons_Channel_Listing),2,FALSE),0)+0
press CTRL + SHIFT + ENTER to confirm instead of just ENTER>

Hi benishiryo,

Amazing!!! It works!! Cool! Nah I don't work there. It is a school assignment. But I would like to understand why there is this +0 at the end, what does it mean? and why "CTRL + SHIFT + ENTER to confirm instead of just ENTER" ? the formula turns out to look like this =IF(C31="Yes",VLOOKUP(TRIM(B31),TRIM(Add_Ons_Channel_Listing),2,FALSE),0)+0

I would like to understand more. You're great! I'm still in shock it works!

Thanks

[dilipandey]

HI Piper,

You received #NAME! error as you are using Excel 2003, below is the revised formula to work in 2003 version:-

Please Login or Register  to view this content.

See rev attachment:- EXCEL ASSIGNMENT - CABLE TV V2.xlsx

Regards,
DILIPandey
<click on below * if this helps>

[piper_co]

HI Piper,

You received #NAME! error as you are using Excel 2003, below is the revised formula to work in 2003 version:-

Please Login or Register  to view this content.

See rev attachment:- Attachment 204756

Regards,
DILIPandey
<click on below * if this helps>

Hi dilipandey,

wow! This works too! Thanks so much! Not sure which formula is a better way to have consisitent vlookup values now.

But really thanks to all!

[dilipandey]

you are welcome Piper..

and its up to you which one you would like to use

Regards,
DILIPandey
<click on below * if this helps>