Vlookup formula help please [Solved/Closed]

Report
Posts
11
Registration date
Monday March 21, 2016
Status
Member
Last seen
March 24, 2016
-
Posts
11
Registration date
Monday March 21, 2016
Status
Member
Last seen
March 24, 2016
-
=IF(PaymentDetails!$B22="","",VLOOKUP(PaymentDetails!$B22,AppMember,4,FALSE))*E22

Please everybody, how do I write this formula so as not to return error #value?

2 replies

Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
Hi Redbaby,

There is nothing wrong with your formula, so it must be the values.

Is the 4th column of the AppMember range a number?
Is the value in E22 a number?

Otherwise consider posting a sample of what you have.

Best regards,
Trowa

1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
11
Registration date
Monday March 21, 2016
Status
Member
Last seen
March 24, 2016

Hello Trowa,

Thank you for your time and effort to help.

=IF(PaymentDetails!$B22="","",VLOOKUP(PaymentDetails!$B22,AppMember,4,FALSE))*E22


Yes the 4th column of AppMember range is a number and the value E22 is also a number but the problem I think is the fact that column D of PaymentDetails is vlookup.

That is to say column B of PaymentDetails is validated members name to vlookup members monthly savings of say 10,000 in column D from another sheet, now if the member pays for say two months the formula will multiply the 10,000 by column E(E22) and return 20,000 in column D and this is correct but all the empty cells are returning the error #value.

Thanks once more, I hope I'm able to make you understand.
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
Hi Redbaby,

If I understand you correctly you want to display nothing instead of #value.

Try this:
=IF(OR(PaymentDetails!$B22="",ISERROR(VLOOKUP(PaymentDetails!$B22,AppMember,4,FALSE))=TRUE),"",VLOOKUP(PaymentDetails!$B22,AppMember,4,FALSE))*E22

Best regards,
Trowa
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
11
Registration date
Monday March 21, 2016
Status
Member
Last seen
March 24, 2016

Thanks a lot,more grace for increase in areas where you desire increase.

Please I need you to advice on the book to read to improve my excel formulas and macros skill.
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
Never read a book about Excel. Google searches, trial/error and ccm.net is what developed my skills.
Posts
11
Registration date
Monday March 21, 2016
Status
Member
Last seen
March 24, 2016

Thanks I appreciate

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!