Forum Discussion
Solved
Vlookup, Match, Index
Hello, in the attached excel sheet, i am trying to improve it. In sheet "Calcs-P-Delta", Cells K25-K42, for each cell, it finds the in sheet J5-2 Column H, the closest value to the value in colu...
Try this:
=INDEX('J5-2'!$H$2:$H$1728,MATCH(MIN(IF('J5-2'!$I$2:$I$1728<>0,ABS(E24*1000-'J5-2'!$H$2:$H$1728),1E+300)),ABS(E24*1000-'J5-2'!$H$2:$H$1728),0))/1000
1) This is not really important - you could also use H10000 as upper limit.
2) 14231.4 is found in H1067 on the Calcs-P-Delta sheet. I1067 = 0, hence the result.
Hans,
Sorry I didn't see the value at the bottom. Since this value 14231 has a corresponding deflection value=0, how can i exclude it? I'd like those functions index and match function to to look up values within the range where both the total force (column H) and the corresponding Actual Deflection( column I) have nonzero values. Is it doable? i believe this will avoid the error.
Thanks,
Sam
Sorry I didn't see the value at the bottom. Since this value 14231 has a corresponding deflection value=0, how can i exclude it? I'd like those functions index and match function to to look up values within the range where both the total force (column H) and the corresponding Actual Deflection( column I) have nonzero values. Is it doable? i believe this will avoid the error.
Thanks,
Sam
Try this:
=INDEX('J5-2'!$H$2:$H$1728,MATCH(MIN(IF('J5-2'!$I$2:$I$1728<>0,ABS(E24*1000-'J5-2'!$H$2:$H$1728),1E+300)),ABS(E24*1000-'J5-2'!$H$2:$H$1728),0))/1000