Can I simplify the below formula?
=IF(VLOOKUP("*"&(LEFT($A3,8))&"*",$D$3:$E$40000,2,FALSE)="Not Available","",VLOOKUP("*"&(LEFT($A3,8))&"*",$D$3:$E$40000,2,FALSE))
I need the cell contents displayed, but not if it says 'Not Available'. Preferably without repeating the entire VLOOKUP formula - a big file I'd like to keep small as possible.
Cell B3 in my demo.
Try this...
=IFERROR(VLOOKUP("*"&(LEFT($A3,8))&"*",$D$3:$E$40000,2,FALSE),"")
Not really sure why you need the "*"&(LEFT($A3,8))&"*" part, but Im sure there is a reason?
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
Can you try this instead?
Hope this helps!
Please Login or Register to view this content.
Spread the love, add to the Rep
"None of us are as smart as all of us."
Maybe just this
=IFERROR(VLOOKUP(A3,$D$3:$E$40000,2,0),"")
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Thanks McMahobot.
IFERROR doesn't work because the value I don't want is a string, not simply an error. "*"&(LEFT($A3,8))&"*" is important because the real document is more complicated. I'm filtering out the first eight digits, which could form only part of the cell it is searching in.
Perhaps you need to provide a sample workbook that is more representative of your actual data, alont with what your expected answer would look like?
Not exactly, it's hard to root out everything which doesn't apply to the question. So long as there isn't confidential info.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks