Forum Discussion
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 column E of "Calcs-P-Delta". For some reason in row 37 of "Calcs-P-Delta", it cant find a value that is equal or close to 29686. You see a value of 29.686 in row 37, but in my equation I multiply the value by 1000 because the values in J5-2 sheet has the 1000 built into the values. I am not sure how to fix the lookup or if there is a better way or function to do the . I appreciate any assistance!
Regards,
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
10 Replies
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.
