Forum Discussion

SamFares's avatar
SamFares
Brass Contributor
Nov 12, 2021
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...
excel
  • HansVogelaar's avatar
    HansVogelaar
    Nov 12, 2021

    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

SamFares's avatar
SamFares
Brass Contributor
Nov 12, 2021
Thank you Hans!
SamFares's avatar
SamFares
Brass Contributor
Nov 12, 2021

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.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 12, 2021

    SamFares 

    ABS(E29*1000-'J5-2'!$H$2:$H$5000) is the absolute value of the difference between E29*1000 and the values in column H on the other sheet. (Absolute value changes negative differences to positive ones)

     

    IF('J5-2'!$I$2:$I$5000<>0,ABS(E29*1000-'J5-2'!$H$2:$H$5000),1E+300) takes these differences but replaces 0 values with an extremely large number 1E+300 = 1 followed by 300 zeros.

     

    MIN(IF('J5-2'!$I$2:$I$5000<>0,ABS(E29*1000-'J5-2'!$H$2:$H$5000),1E+300)) computes the minimum values of these numbers. Because 0s have been replaced with a large number, the 0s are skipped.

  • SamFares's avatar
    SamFares
    Brass Contributor
    Nov 12, 2021
    Thank you so much Hans!
    if you don't mind, could you explain to me this?
    MATCH(MIN(IF('J5-2'!$I$2:$I$5000<>0,ABS(E29*1000-'J5-2'!$H$2:$H$5000),1E+300)

    Thanks,
    Sam

Resources