Index and Match function issue

[mrsushi]

Have a problem where if the fund isn't on the tab, it randomly returns a value. Is this formula the best? Can it be corrected or improved?



INDEX('Paste Summary'!$L:$L,MATCH(C$1,IF('Paste Summary'!$I:$I="Total NAV",'Paste Summary'!$C:$C,"")))



Where C1 = 1150



In the summary tab have column data headed by



Column C - Fund ie 1000, 1150, 1120 etc

Column I – Asset Description

Column L – Values

 

[jasonb75]

Try

=IFERROR(AGGREGATE(15,6,'Paste Summary'!$L$2:$L$1000/('Paste Summary'!$I$2:$I$1000="Total NAV")/('Paste Summary'!$C$2:$C$1000=C$1),1),"")

 

[mrsushi]

Try

=IFERROR(AGGREGATE(15,6,'Paste Summary'!$L$2:$L$1000/('Paste Summary'!$I$2:$I$1000="Total NAV")/('Paste Summary'!$C$2:$C$1000=C$1),1),"")

Thanks Jason, Worked a treat!

 

[mrsushi]

Thanks Jason, Worked a treat!

Just a question, 15 - selection of Aggregate, but what is the 6 representing?

Also, your formula is specifying a range from 2 - 1000. Can this be rewritten as $L:$L?


Similarly, the below is working to some degree, but how can this be rewritten to using your formula as above?
Where C4 = 1115

=INDEX('PASTE LP99'!$W:$W, MATCH(C$4, IF('PASTE LP99'!$B:$B=Split!$A5, 'PASTE LP99'!$E:$E),0))

Many thanks

 

[jasonb75]

From the aggregate options, 15 is used to return the first value that meets the criteria. 6 is used to ignore errors (which are deliberately created to out the random results from your formula).

You can try it with full columns but it is not recommended, it would likely be very slow to process. I went 2-1000 as an example, it would be best to keep it realistic. A full column is over 1 million records (empty cells are still processed if they are included in the formula range), if you're using less than 10k rows then over 99% of the processing effort (and the time it takes) is being wasted.

For the second formula you asked about, are the results still numeric? The method that I suggested will not work with text, although it can be modified to do so if needed.

 

[mrsushi]

From the aggregate options, 15 is used to return the first value that meets the criteria. 6 is used to ignore errors (which are deliberately created to out the random results from your formula).

You can try it with full columns but it is not recommended, it would likely be very slow to process. I went 2-1000 as an example, it would be best to keep it realistic. A full column is over 1 million records (empty cells are still processed if they are included in the formula range), if you're using less than 10k rows then over 99% of the processing effort (and the time it takes) is being wasted.

For the second formula you asked about, are the results still numeric? The method that I suggested will not work with text, although it can be modified to do so if needed.

HI Jason, Many thanks for the information regards to the function.

Regards to the second formula, this is still numeric. Tried to tweak it to work, but not having much success....

 

[jasonb75]

For the second one, again using 2-1000 as an example

=IFERROR(AGGREGATE(15,6,'PASTE LP99'!$W$2:$W$1000/('PASTE LP99'!$B$2:$B$1000=Split!$A5)/('PASTE LP99'!$E$2:$E$1000=C$4),1),"")

The principle of the formula would be rng/(rng1=crit1)/(rng2=crit2) where rng refers to the list to return (the INDEX range), then rng1 and crit1, rng 2 and crit 2, etc are a range to look in and a value to look.

You can add as many pairs of (rngn=critn) as required, with the division operator separating them.

The 1 at the end of the formula is to return the first (only) match found, the double quotes leave the cell blank in the case that there is no match for the given criteria.

Hope that makes sense.

 

[mrsushi]

For the second one, again using 2-1000 as an example

=IFERROR(AGGREGATE(15,6,'PASTE LP99'!$W$2:$W$1000/('PASTE LP99'!$B$2:$B$1000=Split!$A5)/('PASTE LP99'!$E$2:$E$1000=C$4),1),"")

The principle of the formula would be rng/(rng1=crit1)/(rng2=crit2) where rng refers to the list to return (the INDEX range), then rng1 and crit1, rng 2 and crit 2, etc are a range to look in and a value to look.

You can add as many pairs of (rngn=critn) as required, with the division operator separating them.

The 1 at the end of the formula is to return the first (only) match found, the double quotes leave the cell blank in the case that there is no match for the given criteria.

Hope that makes sense.

Many thanks Jason for the explanation and many thanks for making the formula work.
Much appreciated.
Regards
M