Forum Discussion
Vlookup, Match, Index
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
MATCH('Calcs-p1'!E37*1000,'J5-2'!H:H,1) will only return a meaningful result if the lookup range 'J5-2'!H:H is sorted in ascending order. It clearly isn't.
Try this in K24:
=INDEX('J5-2'!$H$2:$H$1728,MATCH(MIN(ABS('Calcs-p1'!E24*1000-'J5-2'!$H$2:$H$1728)),ABS('Calcs-p1'!E24*1000-'J5-2'!$H$2:$H$1728),0))/1000
If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.
Thank you so much, Hans!
I made the change and it worked.
1. You used row H1728 as an upper limit, Is there a way to go up to the last value in column "I" that is a non zero. So in this case it will be row 1063. This value is a variable.
2. The other issue that I ran into is in column N in "Calcs-P-Delta" sheet( I renamed the sheet). In row 28, I don't see the value in cell K28=14.2314 or 14,231 present in sheet J5-2 column H. i was trying to investigate why "Actual delta" in column N of sheet "Calcs-P-Delta" is zero in row 28. My equation might need correction. Please see attached.
Thank you,
Sam1) 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
