INDEX AND MATCH LOOKING FOR VALUE NOT GIVEN (INCREMENT)

rcomag

rcomag

New Member
Joined
Aug 23, 2019
Messages
37
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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.
 

Attachments

  • Screenshot (513).png
    Screenshot (513).png
    51.6 KB · Views: 10

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Try this:
Book1
BCDEFGHIJKLMNO
11
12T.D. No.PropertyValueYears From
1397-2001-00206San Francisco10001973-2021San Francisco97-2001-0020619995000
1497-2001-00206San Francisco20002022San Francisco97-2001-0020620204000
1597-2001-00206San Francisco30002021San Francisco97-2001-0020620213000
1697-2001-00206San Francisco40002020
1797-2001-00206San Francisco50001998
1897-2001-00206San Francisco60001994
1997-2001-00206San Francisco70001993
2097-2001-00206San Francisco80001992
2197-2001-00206San Francisco90001985
2297-2001-00206San Francisco100001974
2397-2001-00206San Francisco110001973
24
25
Sheet1
Cell Formulas
RangeFormula
N13:N15N13=AGGREGATE(15,6,($E$13:$E$23)/(($B$13:$B$23=L13)*($C$13:$C$23=K13)*($H$13:$H$23<=M13)),1)
 
Upvote 0
followed the formula you have given but it does not give the correct value for the given year
 

Attachments

  • Screenshot (514).png
    Screenshot (514).png
    64.4 KB · Views: 3
Upvote 0
1. please fix E21 at formula with F4 key ($E$21).
2. please upload example data below description below of my post.
 
Upvote 0
sorry, I don't know how to upload the file. lock the cell (f4) already but still the same result
 

Attachments

  • Screenshot (515).png
    Screenshot (515).png
    65.2 KB · Views: 3
Upvote 0
with this condition, I cannot find exact problem. maybe your criterias don't same with your range and have some extra space at the first or end.
you see with my uploaded file formula works correctly.
also if you click on Xl2BB below of my post, you can read instruction to how to work with it. and if cannot use second option (googledrive, ...)
 
Upvote 0
I change the aggregate formula from 15 to 14, some value were right but the others are not.


sample AR test.xlsx
M
352,330.00
Sheet1
Cell Formulas
RangeFormula
M35M35=AGGREGATE(14,6,($D$21:$D$392)/(($B$21:$B$392=N35)*($C$21:$C$392=L35)*($E$21:$E$392<=O35)),1)
 
Upvote 0
sample AR test.xlsx
BCDELMNOPQR
11ASSESSEDYEARS
12T.D. NO.PROPERTY VALUEFROM
2197-001-00206San Francisco6,810.001973-2021namevaluenumberyear
2297-001-00206San Francisco6,810.002022=VLOOKUP(S22,B13:C126,2,FALSE) =VLOOKUP(S22&"-"&T22,F13:J117,5,FALSE) 97-001-002222020
2397-001-00206San Francisco6,810.002021Rotunda#N/A97-001-002221998
2497-001-00206San Francisco6,810.002020San Francisco#N/A97-001-002061999
2597-001-00206San Francisco6,810.001998San Francisco#N/A97-001-002062020
2697-001-00206San Francisco3,420.001994San Francisco =INDEX(J:J,MATCH(S26&T26,G:G&H:H,0)) 97-001-002062021
2797-001-00206San Francisco3,310.001993San Francisco3,420.0097-001-002061994
2897-001-00206San Francisco2,330.001992San Francisco3,310.0097-001-002061993
2997-001-00206San Francisco2,330.001985 Añir, Celedonio1,530.0097-001-003311993
3097-001-00206San Francisco1,360.001980 =AGGREGATE(15,6,(E24:$E$32)/(($B$21:$B$32=S30)*($C$21:$C$32=O30)*($H$21:$H$32<=T30)),1)
3197-001-00206San Francisco1,360.001974
3297-001-00206San Francisco1,360.001973FOLLOWED THE FORMULA BUT IT DOES NOT GET THE CORRECT VALUE FOR THE GIVEN YEAR
3397-001-00222Rotunda4,080.001980-2021
3497-001-00222Rotunda4,080.002022
3597-001-00222Rotunda4,080.002021San Francisco2,330.0097-001-002061985
3697-001-00222Rotunda4,080.002020San Francisco3,310.0097-001-002061993
3797-001-00222Rotunda4,080.001998Rotunda5,670.0097-001-002221993
3897-001-00222Rotunda2,430.001994Pine Street32,010.0097-001-002601993
3997-001-00222Rotunda2,240.001993San Francisco3,310.0097-001-002061993
4097-001-00222Rotunda5,670.001992
4197-001-00222Rotunda5,670.001985
4297-001-00222Rotunda3,700.001980
4397-001-00260Pine Street4,750.001973-2021
4497-001-00260Pine Street4,750.002022
4597-001-00260Pine Street4,750.002021
4697-001-00260Pine Street4,750.002020
4797-001-00260Pine Street4,750.001998
4897-001-00260Pine Street2,390.001994
4997-001-00260Pine Street2,310.001993
5097-001-00260Pine Street5,300.001992
5197-001-00260Pine Street5,300.001985
5297-001-00260Pine Street3,100.001980
5397-001-00260Pine Street32,010.001974
5497-001-00260Pine Street32,010.001973
Sheet1
Cell Formulas
RangeFormula
L23:L24L23=VLOOKUP(N23,B14:C106,2,FALSE)
M23M23=VLOOKUP(N23&"-"&O23,E14:G97,5,FALSE)
M24:M25M24=INDEX(G:G,MATCH(N24&O24,#REF!&E:E,0))
L25L25=VLOOKUP(N25,B16:C107,2,FALSE)
L26L26=VLOOKUP(N26,B16:C32,2,FALSE)
L27L27=VLOOKUP(N27,B21:C392,2,FALSE)
M35:M39,M27:M29M27=AGGREGATE(14,6,($D$21:$D$392)/(($B$21:$B$392=N27)*($C$21:$C$392=L27)*($E$21:$E$392<=O27)),1)
L28L28=VLOOKUP(N28,B21:C392,2,FALSE)
L29L29=VLOOKUP(N29,B21:C392,2,FALSE)
C44:C54,C34:C42,C22:C32C22=C21
L35:L39L35=VLOOKUP(N35,$B$21:$C$392,2,FALSE)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
I use 4 version for you. you look which one fit with result you want.
and if all version give wrong result, please tell exact what result want for these 5 case.
Book1.xlsx
ABCDELMNO
20
2197-001-00206San Francisco68101973-2021namevaluenumberyear
2297-001-00206San Francisco68102022#N/A =VLOOKUP(S22&"-"&T22,F13:J117,5,FALSE) 97-001-002222020
2397-001-00206San Francisco68102021San Francisco =INDEX(J:J,MATCH(S26&T26,G:G&H:H,0)) 97-001-002062021
2497-001-00206San Francisco68102020San Francisco2330.0097-001-002061985
2597-001-00206San Francisco68101998San Francisco3310.0097-001-002061993
2697-001-00206San Francisco34201994Rotunda2240.0097-001-002221993
2797-001-00206San Francisco33101993Pine Street2310.0097-001-002601993
2897-001-00206San Francisco23301992San Francisco3310.0097-001-002061993
2997-001-00206San Francisco23301985
3097-001-00206San Francisco13601980
3197-001-00206San Francisco13601974
3297-001-00206San Francisco13601973San Francisco1360.0097-001-002061985
3397-001-00222Rotunda40801980-2021San Francisco1360.0097-001-002061993
3497-001-00222Rotunda40802022Rotunda2240.0097-001-002221993
3597-001-00222Rotunda40802021Pine Street2310.0097-001-002601993
3697-001-00222Rotunda40802020San Francisco1360.0097-001-002061993
3797-001-00222Rotunda40801998
3897-001-00222Rotunda24301994
3997-001-00222Rotunda22401993San Francisco2330.0097-001-002061985
4097-001-00222Rotunda56701992San Francisco3310.0097-001-002061993
4197-001-00222Rotunda56701985Rotunda5670.0097-001-002221993
4297-001-00222Rotunda37001980Pine Street32010.0097-001-002601993
4397-001-00260Pine Street47501973-2021San Francisco3310.0097-001-002061993
4497-001-00260Pine Street47502022
4597-001-00260Pine Street47502021
4697-001-00260Pine Street47502020San Francisco6810.0097-001-002061985
4797-001-00260Pine Street47501998San Francisco6810.0097-001-002061993
4897-001-00260Pine Street23901994Rotunda4080.0097-001-002221993
4997-001-00260Pine Street23101993Pine Street4750.0097-001-002601993
5097-001-00260Pine Street53001992San Francisco6810.0097-001-002061993
5197-001-00260Pine Street53001985
5297-001-00260Pine Street31001980
5397-001-00260Pine Street320101974
5497-001-00260Pine Street320101973
Sheet2
Cell Formulas
RangeFormula
L22L22=VLOOKUP(S22,B13:C126,2,FALSE)
L23L23=VLOOKUP(N23,B16:C32,2,FALSE)
L46:L50,L39:L43,L32:L36,L24:L28L24=VLOOKUP(N24,$B$21:$C$392,2,FALSE)
M24:M28M24=AGGREGATE(15,6,($D$21:$D$392)/(($B$21:$B$392=N24)*($C$21:$C$392=L24)*($E$21:$E$392>=O24)),1)
C44:C54,C34:C42,C22:C32C22=C21
M32:M36M32=AGGREGATE(15,6,($D$21:$D$392)/(($B$21:$B$392=N32)*($C$21:$C$392=L32)*($E$21:$E$392<=O32)),1)
M39:M43M39=AGGREGATE(14,6,($D$21:$D$392)/(($B$21:$B$392=N39)*($C$21:$C$392=L39)*($E$21:$E$392<=O39)),1)
M46:M50M46=AGGREGATE(14,6,($D$21:$D$392)/(($B$21:$B$392=N46)*($C$21:$C$392=L46)*($E$21:$E$392>=O46)),1)
 
Upvote 0
Sir, looking at the table, only the first part does give the correct return value on the given number and year. Will try to practice on a larger data. How will the formula run if the table is on another sheet or in another file with a multiple sheets. Thank you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

szita2000
  • Question
Index Match Match from a named table to a range
Replies
1
Views
357
Alex Blakenburg
A
N
  • Question
MATCH and INDEX help
Replies
3
Views
107
etaf
etaf
S
  • Solved
Lookup using IF/Match returning wrong values
Replies
2
Views
153
sundodger
S
A
  • Question
Index and Match for 2No criteria only partially returning values other values #ref error.
Replies
6
Views
166
roelandwatteeuw
R
D
  • Question
INDEX MATCH FORMULA N/A# ERROR
Replies
4
Views
226
Alex Blakenburg
A

Forum statistics

Threads
1,215,494
Messages
6,125,139
Members
449,207
Latest member
VictorSiwiide

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top