Hi=IF(ISNUMBER(INDEX(data!D:D,MATCH(A6&B6,data!A:A,0))),INDEX(data!D:D,MATCH(A6&B6,data!A:A,0)),0)
Can someone pls amend the above formula so that it accounts for a condition when i have a blank cell and it must return this as blank too...and NOT 0.
Thanks in advance
Suju
Land Down-Under... Over the Hill and Farrrr Away.
Originally Posted by SUJU
=IF(ISNA(MATCH(A6&B6,data!A:A,0)),"", IF(ISNUMBER(INDEX(data!D:D,MATCH(A6&B6,data!A:A,0))), INDEX(data!D:D,MATCH(A6&B6,data!A:A,0)),0))
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
xld, why the extra call???? You lost me there ..
Why not just ...
=IF(ISNUMBER(INDEX(data!D:D,MATCH(A6&B6,data!A:A,0))),INDEX(data!D:D,MATCH(A6&B6,data!A:A,0)),"")
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Fogged brain I guess!
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
LOL! Well .. uh .. try harder next time!! ROFL!!
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Noted (where is the hangs head in shame smiley>)
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Thnx Fireftr and XLD. I have tried that option before. My problem is as follows:
IF the value in data! DISNUMBER, then return that number (including 0's).
IF the value in ISERROR, then return 0
IF ISTEXT then return blank
IF ISBLANK, then return BLANK
IF is #N/A N.A return a 0
I cant figure out how to write the code for these different happenings.
Your input is appreciated.
Suju
Land Down-Under... Over the Hill and Farrrr Away.
I would suggest putting this in a seperate column/cell ..
Then you could easily reference it as such (assuming the cell it goes into is cell E6 and down)...=INDEX(data!D:D,MATCH(A6&B6,data!A:A,0))
Does this work for you?=IF(E6="","",IF(OR(ISTEXT(E6),ISBLANK(E6)),0,IF(ISERROR(E6),0,IF(ISNUMBER(E6),E6,""))))
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Posting Permissions
Reply With Quote
