IF(ISERROR) with Index/Match formula
Hi,
I have an array formula that is working fine so far:
=INDEX(PIVOT!$E$3:$E$3000;MATCH(1;(PIVOT!$B$3:$B$3000=$F6)*(PIVOT!$D$3:$D$3000=1);0))
but as soon as I create an IF(ISERROR) formula out of it (to avoid n/a cells where no match found), all cells turn "0", even those which previously showed the correct result...
=IF(ISERROR(INDEX(PIVOT!$E$3:$E$3000;MATCH(1;(PIVOT!$B$3:$B$3000=$F7)*(PIVOT!$D$3:$D$3000=1);0)));0; INDEX(PIVOT!$E$3:$E$3000;MATCH(1;(PIVOT!$B$3:$B$3000=$F7)*(PIVOT!$D$3:$D$3000=1))))
Anybody can help me please?
many thanks,
N.
Last edited by ninuskka; 01-07-2011 at 01:11 PM.
Did you reconfirm it with CTRL+SHIFT+ENTER?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
An alternative might be:
which does not need to be confirmed with CTRL+SHIFT+ENTER... You can just use ENTER=LOOKUP(9.99999E+307;CHOOSE({1;2};0;INDEX(PIVOT!$E$3:$E$3000;MATCH(1;INDEX((PIVOT!$B$3:$B$3000=$F7)*(PIVOT!$D$3:$D$3000=1);0);0))))
or possibly even:
with ENTER only=SUMPRODUCT(PIVOT!$E$3:$E$3000,(PIVOT!$B$3:$B$3000=$F7)*(PIVOT!$D$3:$D$3000=1))
Last edited by NBVC; 01-07-2011 at 11:17 AM.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Yes, I´ve used ctrl+shift+enter in both cases...
thanks for your reply,
unfortunately the data cannot be sorted in ascending order ...
any other thoughts?
I never said anything about sorting
is the result supposed to be a number? If yes, Did you try the sumproduct? With the Sumproduct, replace all the commas with semi-colons:
=SUMPRODUCT(PIVOT!$E$3:$E$3000;(PIVOT!$B$3:$B$3000=$F7)*(PIVOT!$D$3:$D$3000=1))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
sorry for confusion - thought the data has to be sorted when using the LOOKUP formula ...
the SUMPRODUCT works great - even with IF(ISERROR)!!!
many thanks for your help!
You shouldn't need IF(ISERROR() with SUMPRODUCT() if you want a result of 0 when matches not found.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks