Array Forumla-Help #NUM!
Good afternoon,
i currently have the formula below (mulitiple lookup-horizontal). if there is no data to return, i currently get the following error message; #NUM!.
Is there anything i can add to stop this happening to leave a blank cell instead.
=INDEX($DS$6:$DS$43548, SMALL(IF($CU6=$DP$6:$DP$43548, ROW($DP$6:$DP$43548)-MIN(ROW($DP$6:$DP$43548))+1, ""), COLUMN(B4)))
thank you
Hi and welcome to the forum.
As you are in Excel 2003, try this.
Is this helps you?
Please Login or Register to view this content.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
You can try this formula
=IFERROR(INDEX($DS$6:$DS$43548, SMALL(IF($CU6=$DP$6:$DP$43548, ROW($DP$6:$DP$43548)-MIN(ROW($DP$6:$DP$43548))+1, ""), COLUMN(B4),""))
Oeysbrei
Sorry, both are returning errors.
can you post a samplebook?
IFERROR function is only available in Excel 2007 and later. In Excel 2003 try this version
=IF(COLUMN(B4)>COUNTIF($DP$6:$DP$43548,$CU6),"", INDEX($DS$6:$DS$43548,SMALL(IF($CU6=$DP$6:$DP$43548,ROW($DP$6:$DP$43548)-MIN(ROW($DP$6:$DP$43548))+1),COLUMN(B4))))
confirm with CTRL+SHIFT+ENTER
Audere est facere
from what i can see, Fotis's formula should work, but he left off the end part..
also check to see if your (country) version of supports ; (semi-colon) or if you need to change it to , (comma)Please Login or Register to view this content.
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
I refer back to my original posting.
Is there another formula I could use to produce the same results of the lookup. Please ignore the #num! Error for now.
I want to avoid having to use an array, therefore not having to press ctrl+shift+enter.
I need a lookup which will display results horizontally.
This version should work without CTRL+SHIFT+ENTER
=IF(COLUMN(B4)>COUNTIF($DP$6:$DP$43548,$CU6),"", INDEX($DS$6:$DS$43548,LARGE(INDEX(($CU6=$DP$6:$DP$43548)*(ROW($DP$6:$DP$43548)-MIN(ROW($DP$6:$DP$43548))+1),0),COUNTIF($DP$6:$DP$43548,$CU6)-COLUMN(B4)+1)))
thank you.
slight issue, formula appears to be picking up second available result first, and is missing out the first possible result from the lookup data completely?
any ideas?
thank you in advance.
Sorted now, thanks
I used the same formula that you suggested in post 1 which will pick up the second result. Better, in fact, to use COLUMNS function rather than COLUMN, that will be robust (formula will still return the correct results if you insert columns), e.g. if the first result should be in C2 use this version in C2 copied across
=IF(COLUMNS($C2:C2)>COUNTIF($DP$6:$DP$43548,$CU6),"", INDEX($DS$6:$DS$43548,LARGE(INDEX(($CU6=$DP$6:$DP$43548)*(ROW($DP$6:$DP$43548)-MIN(ROW($DP$6:$DP$43548))+1),0),COUNTIF($DP$6:$DP$43548,$CU6)-COLUMNS($C2:C2)+1)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks