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
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
- Thank you Hans!
Hans,
I ran into this issue in row 29 of "Calcs-P-Delta", in column "N" it couldn't look up the corresponding actual deflection and it shows "N/A". Please see attached.See the attached version.