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]

For other sheet, for each range you should go to that sheet and select range, the range added to formula and then look at formula and use same structure for other sheets. For example if your table is in Sheet6 then first range is:
Sheet6!$D$21:$D$392

 

[rcomag]

how would I make it, =(sheet6!d21:d392)*aggregate(15,6(d21:d392)................... Can it find the value in the other sheet or i should use vlookup.

 

[maabadi]

if your Data is in Sheet6 and your criteria at the sheet you want write formula, formula changes to:

=AGGREGATE(15,6,(Sheet6!$D$21:$D$392)/((Sheet6!$B$21:$B$392=N24)*(Sheet6!$C$21:$C$392=L24)*(Sheet6!$E$21:$E$392>=O24)),1)

and if is in another Workbook ( with name Book1.xlsm here) at Sheet Paper, formula changes to:

=AGGREGATE(15,6,('[Book1.xlsm]Paper'!$D$21:$D$392)/(('[Book1.xlsm]Paper'!$B$21:$B$392=N24)*(Sheet6!'[Book1.xlsm]Paper'!$C$21:$C$392=L24)*('[Book1.xlsm]Paper'!$E$21:$E$392>=O24)),1)

and about table, if you create Table at Excel , you should Replace Ranges with Column name at Table.

 

[rcomag]

Good Afternoon! used the formula in between sheets, when I put a data (higher year) not stated in the table, an error appears (#NUM!).

 

[maabadi]

When you use Higher value from range this part of formula

Sheet6!$E$21:$E$392>=O24

give you nothing, because all value at range smaller than criteria, then you see error

 

[rcomag]

When you use Higher value from range this part of formula

Sheet6!$E$21:$E$392>=O24

give you nothing, because all value at range smaller than criteria, then you see error

In the practice sheet that I made, when I put a higher value on the data year, it returns the latest value. example.. data table latest year 2022 with value 1,000.00. When placing data with year 2023 or higher, it returns the value from 2022. When I sampled it with between sheets, I get the #NUM! in return when putting higher year.

 

[maabadi]

1. Are you sure you have >= Not <= ?
2. maybe you range inputted in formula don't equal exact with your source data range, also check it?

 

[rcomag]

AR for OR.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3
4
5	Address	Value	Number	Year
6	Barobaybay, Magarao, Camarines Sur	#NUM!	97-001-00146	2023
7	Barobaybay, Magarao, Camarines Sur	3,310.00	97-001-00206	1993
8	Monserrat, Magarao, Camarines Sur	2,310.00	97-001-00260	1993
9	Barobaybay, Magarao, Camarines Sur	3,310.00	97-001-00206	1993
10	Barobaybay, Magarao, Camarines Sur	2,240.00	97-001-00222	1993
11	Monserrat, Magarao, Camarines Sur	2,310.00	97-001-00260	1993
12	Barobaybay, Magarao, Camarines Sur	2,330.00	97-001-00206	1985
13	Barobaybay, Magarao, Camarines Sur	2,240.00	97-001-00222	1993
14	Punong, Magarao, Camarines Sur	1,030.00	97-001-00031	1971
15	Punong, Magarao, Camarines Sur	23,040.00	97-001-00031	1994
16	Punong, Magarao, Camarines Sur	22,320.00	97-001-00031	1993
17	Punong, Magarao, Camarines Sur	#NUM!	97-001-00031	2023
18	Punong, Magarao, Camarines Sur	45,860.00	97-001-00031	2022
19
20
21	=AGGREGATE(15,6,(barobaybay!$I$13:$I$2000)/((barobaybay!$A$13:$A$2000=C17)*(barobaybay!$E$13:$E$2000=A17)*(barobaybay!$J$13:$J$2000>=D17)),1)
Sheet1

Cell Formulas
Range	Formula
A6:A18	A6	=VLOOKUP(C6,barobaybay!$A$21:$M$8000,5,FALSE)
B6:B18	B6	=AGGREGATE(15,6,(barobaybay!$I$13:$I$2000)/((barobaybay!$A$13:$A$2000=C6)*(barobaybay!$E$13:$E$2000=A6)*(barobaybay!$J$13:$J$2000>=D6)),1)

 

[maabadi]

Are you sure your data started from row 13 at barobaybay sheet?

 

[rcomag]

Are you sure your data started from row 13 at barobaybay sheet?

yes sir, the data table starts at row 13