INDEX AND MATCH LOOKING FOR VALUE NOT GIVEN (INCREMENT)

[rcomag]

Found a formula in youtube regarding index and match. My problem is how can it return a value that is not indicated in the table (in between value) like in row 24, where the return value is N/A from the year indicated which is not in the table.

 

[maabadi]

Try this:

=IFERROR(AGGREGATE(15,6,(barobaybay!$I$13:$I$2000)/((barobaybay!$A$13:$A$2000=C6)*(barobaybay!$J$13:$J$2000>=D6)),1),AGGREGATE(15,6,(barobaybay!$I$13:$I$2000)/((barobaybay!$A$13:$A$2000=C6)*(barobaybay!$J$13:$J$2000>=AGGREGATE(14,6,(barobaybay!$J$13:$J$2000)/(barobaybay!$A$13:$A$2000=C6),1))),1)

 

[rcomag]

Try this:

=IFERROR(AGGREGATE(15,6,(barobaybay!$I$13:$I$2000)/((barobaybay!$A$13:$A$2000=C6)*(barobaybay!$J$13:$J$2000>=D6)),1),AGGREGATE(15,6,(barobaybay!$I$13:$I$2000)/((barobaybay!$A$13:$A$2000=C6)*(barobaybay!$J$13:$J$2000>=AGGREGATE(14,6,(barobaybay!$J$13:$J$2000)/(barobaybay!$A$13:$A$2000=C6),1))),1)

Thank you very much sir! If I use it to look for multiple sheets, =IFERROR(AGGREGATE(15,6,(barobaybay!$I$13:$I$2000)/((barobaybay!$A$13:$A$2000=C6)*(barobaybay!$J$13:$J$2000>=D6)),1),AGGREGATE(15,6,(barobaybay!$I$13:$I$2000)/((barobaybay!$A$13:$A$2000=C6)*(barobaybay!$J$13:$J$2000>=AGGREGATE(14,6,(barobaybay!$J$13:$J$2000)/(barobaybay!$A$13:$A$2000=C6),1))),1....., Could I copy the formula and paste it at the end of the formula, will just change the sheet name.. Should I start copying from the iferror or just the aggregate.... '=IFERROR(AGGREGATE(15,6,(barobaybay!$I$13:$I$2000)/((barobaybay!$A$13:$A$2000=C27)*(barobaybay!$J$13:$J$2000>=D27)),1),AGGREGATE(15,6,(barobaybay!$I$13:$I$2000)/((barobaybay!$A$13:$A$2000=C27)*(barobaybay!$J$13:$J$2000>=AGGREGATE(14,6,(barobaybay!$J$13:$J$2000)/(barobaybay!$A$13:$A$2000=C27),1))),1))*(AGGREGATE(15,6,(bell!$I$13:$I$2000)/((bell!$A$13:$A$2000=C27)*(bell!$J$13:$J$2000>=D27)),1),AGGREGATE(15,6,(bell!$I$13:$I$2000)/((bell!$A$13:$A$2000=C27)*(bell!$J$13:$J$2000>=AGGREGATE(14,6,(bell!$J$13:$J$2000)/(bell!$A$13:$A$2000=C27),1)))

 

[maabadi]

You're welcome.
Why you don't replace previous formula with this. whenever you need you can sheet name and range based your data range. No problem.

 

[rcomag]

You're welcome.
Why you don't replace previous formula with this. whenever you need you can sheet name and range based your data range. No problem.

Sir, what if there are duplications in the number but with different names and value.