I received "#VALUE!" when performing a range lookup that consist of blank cells [=LOOKUP(J4:BS4,{"VD1","VM3","VV1","VD2","VX1","VP1","INT"},{"PD","PD","PD","PD","PD","PD","INT"})].
May I know how to ignore the blank cells within J4:BS4? I'm open to any other formula as long as it produces same result. For example, when "VD1" is found within range, it return result as "PD".
Thank you for reading this thread. I would really appreciate if anyone can help me.
Warmest regards,
Brandon
Hi and welcome to the forum
I dont use lookup() that much, but Im pretty sure that the search criteria needs to be a single reference, not a range
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi Ford,
Thank you for your warm welcome & comment. Sorry I didn't know that search criteria doesn't allow range. May I know is there any other solution that can rectify my situation? For example, I need to lookup "VD1" in a range (J4:BS4) and return the result as "PD" in another cell (H4) if anyone of "VD1/VM3/VP1" is found within this range. It will return the result of "INT" when "INT" is found among the status of "VD1/VM3/VP1/INT" within same range. Kindly note that range J4:BS4 have more than 1 status (VD1, VM3, VP1, INT).
I'm really new in Excel but keen to learn. May I hope to get some help from this resourceful forum. Thank you.
Last edited by Brand012; 01-03-2014 at 03:30 AM. Reason: overlook on name
Based on your formula, you want to search for a range of criteria, and if found, return various text, based on what was found?
....find "VD1","VM3","VV1","VD2","VX1","VP1","INT" and if found, return "PD","PD","PD","PD","PD","PD","INT"...?
If this is the case, we would probably need to use a bunch of IF() and match functions. However, I do see in your data that you have a few different matches withing a row/range. Which match would you want returned? Perhaps if you could privide a few sample (manual?) answers?
Hi Ford,
Thanks for helping me again. I'm sorry that my previous spread sheet (Sample.xlsx) was not updated. If you don't mind to refer Sample 2.xlsx attached? In fact I would like to lookup multiple status in "I4:BR4" and return the result in "H4", and I think it should be a bunch of IF conditions like you suggest.
If: "VD1","VD2","VM3","VX1","VP1" is found within I4:BR4, return "Pending Installation" in H4
If: "INT" is found among any other status (VD1/VD2/VM3/VX1/VP1), return "Installed" in H4
Multiple status ("VD1","VD2","VM3","VX1","VP1", "INT") will add into "I4:BR4" from time-to-time. May I know what is the recommended formula for this?
Regards,
Brandon
Try this array formula to see if you get the desired result. As this is an array formula so it needs to be confirmed with Ctrl+Shift+Enter and then drag down.
Please Login or Register to view this content.
Last edited by sktneer; 01-03-2014 at 11:59 PM.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Hi Sktneer,
Thank you so much. Your formula solved my problem. I really appreciate your most valuable help. May you & your loved ones have a nice weekend ahead.
warmest regards,
Brandon
Glad to help you.
If you are satisfied with the answer provided, you may click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum.
Hi Ford & Sktneer,
I'm very thankful to both of you for spending your valuable time & efforts to help me in this forum.
Thank you all for making a happy & fruitful week for me.
Brandon
Happy to help and thank your the kind words
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks