Hi all, so the formula below works fine apart from when there are any blank cells down Sheet2!$G$3:$G$220 then formula returns a zero. can anyone amend the formula below please so it returns a blank in this instance not a zero please?
=INDEX(Sheet2!$G$3:$G$220,MATCH(A3,Scores2!$C$3:$C$220,0))
many thanks
What type of data does the formula normally return?
Is it text? Numbers? Could be both? Something else? If it's numbers, is 0 an otherwise valid result?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hi, Thanks for you question, down Sheet2!G:G there is always either a two digit number or a blank. so my formula is returning the the two digit number if present, but a zero if there is a blank. can i get the blank to be replicated also?
Try this...
=IFERROR(1/(1/INDEX(Sheet2!$G$3:$G$220,MATCH(A3,Scores2!$C$3:$C$220,0))),"")
hi, that works in returning a blank for a blank, but also returns a blank when it should return a number. when i paste the formula it also brings up a save box to 'Upadate Values'?
Try this
=IF(INDEX(Sheet2!$G$3:$G$220,MATCH(A3,Scores2!$C$3:$C$220,0))=0,"",INDEX(Sheet2!$G$3:$G$220,MATCH(A3,Scores2!$C$3:$C$220,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
i have it sorry, turns out its a &"" at the end. many thanks for your time and effort, much appreciated.
Are you sure your numbers are true numeric numbers?Originally Posted by fruit&veg
What result do you get with this formula:
=COUNT(Sheet2!$G$3:$G$220)
That sounds like you're referencing a sheet that does not exist.when i paste the formula it also brings up a save box to 'Upadate Values'?
You posted this formula in post #1:
I used the same basic formula in my reply:=INDEX(Sheet2!$G$3:$G$220,MATCH(A3,Scores2!$C$3:$C$220,0))
=IFERROR(1/(1/INDEX(Sheet2!$G$3:$G$220,MATCH(A3,Scores2!$C$3:$C$220,0))),"")
Good deal. Thanks for the feedback!
Sorry, the save thing was me being an idiot. turns out it was also the reason why your formula wouldn't work for me originally. on the surface i thought the &"" had worked, but turns out it had disastrous effects further along my other sheets/formulas. i have just input your formula, and it does work and also keeps all my other sheets working correctly!...so long story short your clearly a genius! thank you very much, its much appreciated.
You're welcome!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks