SOLVED

Vlookup, Match, Index

%3CLINGO-SUB%20id%3D%22lingo-sub-2957003%22%20slang%3D%22en-US%22%3EVlookup%2C%20Match%2C%20Index%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2957003%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3Ein%20the%20attached%20excel%20sheet%2C%20i%20am%20trying%20to%20improve%20it.%20In%20sheet%20%22Calcs-P-Delta%22%2C%20Cells%20K25-K42%2C%26nbsp%3B%20for%20each%20cell%2C%20it%20finds%20the%26nbsp%3B%20in%20sheet%20J5-2%20Column%20H%2C%26nbsp%3B%20the%20closest%26nbsp%3B%20value%20to%20the%20value%20in%20column%20E%20of%20%22Calcs-P-Delta%22.%20For%20some%20reason%20in%20row%2037%20of%26nbsp%3B%22Calcs-P-Delta%22%2C%20it%20cant%20find%20a%20value%20that%20is%20equal%20or%20close%20to%2029686.%20You%20see%20a%20value%20of%2029.686%20in%20row%2037%2C%20but%20in%20my%20equation%20I%20multiply%20the%20value%20by%201000%20because%20the%20values%20in%20J5-2%20sheet%20has%20the%201000%20built%20into%20the%20values.%20I%20am%20not%20sure%20how%20to%20fix%26nbsp%3B%20the%20lookup%20or%20if%20there%20is%20a%20better%20way%26nbsp%3B%20or%20function%20to%20do%20the%20.%20I%20appreciate%20any%20assistance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3ESam%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2957003%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2958131%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%2C%20Match%2C%20Index%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2958131%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351459%22%20target%3D%22_blank%22%3E%40SamFares%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMATCH('Calcs-p1'!E37*1000%2C'J5-2'!H%3AH%2C1)%20will%20only%20return%20a%20meaningful%20result%20if%20the%20lookup%20range%20'J5-2'!H%3AH%20is%20sorted%20in%20ascending%20order.%20It%20clearly%20isn't.%3C%2FP%3E%0A%3CP%3ETry%20this%20in%20K24%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX('J5-2'!%24H%242%3A%24H%241728%2CMATCH(MIN(ABS('Calcs-p1'!E24*1000-'J5-2'!%24H%242%3A%24H%241728))%2CABS('Calcs-p1'!E24*1000-'J5-2'!%24H%242%3A%24H%241728)%2C0))%2F1000%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20don't%20have%20Microsoft%20365%20or%20Office%202021%2C%20confirm%20the%20formula%20with%20Ctrl%2BShift%2BEnter.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2958904%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%2C%20Match%2C%20Index%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2958904%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much%2C%20Hans!%3CBR%20%2F%3EI%20made%20the%20change%20and%20it%20worked.%3CBR%20%2F%3E1.%20You%20used%20row%20H1728%20as%20an%20upper%20limit%2C%20Is%20there%20a%20way%20to%20go%20up%20to%20the%20last%20value%20in%20column%20%22I%22%20that%20is%20a%20non%20zero.%20So%20in%20this%20case%20it%20will%20be%20row%201063.%20This%20value%20is%20a%20variable.%3CBR%20%2F%3E2.%20The%20other%20issue%20that%20I%20ran%20into%20is%20in%20column%20N%20in%20%22Calcs-P-Delta%22%20sheet(%20I%20renamed%20the%20sheet).%20In%20row%2028%2C%20I%20don't%20see%20the%20value%20in%20cell%20K28%3D14.2314%20or%2014%2C231%20present%20in%20sheet%20J5-2%20column%20H.%20i%20was%20trying%20to%20investigate%20why%20%22Actual%20delta%22%20in%20column%20N%20of%20sheet%20%22Calcs-P-Delta%22%20is%20zero%20in%20row%2028.%20My%20equation%20might%20need%20correction.%20Please%20see%20attached.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%2C%3CBR%20%2F%3ESam%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2959086%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%2C%20Match%2C%20Index%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2959086%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351459%22%20target%3D%22_blank%22%3E%40SamFares%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1)%20This%20is%20not%20really%20important%20-%20you%20could%20also%20use%20H10000%20as%20upper%20limit.%3C%2FP%3E%0A%3CP%3E2)%2014231.4%20is%20found%20in%20H1067%20on%20the%20Calcs-P-Delta%20sheet.%20I1067%20%3D%200%2C%20hence%20the%20result.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2959178%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%2C%20Match%2C%20Index%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2959178%22%20slang%3D%22en-US%22%3EHans%2C%3CBR%20%2F%3ESorry%20I%20didn't%20see%20the%20value%20at%20the%20bottom.%20Since%20this%20value%2014231%20has%20a%20corresponding%20deflection%20value%3D0%2C%20how%20can%20i%20exclude%20it%3F%20I'd%20like%20those%20functions%20index%20and%20match%20function%20to%20to%20look%20up%20values%20within%20the%20range%20where%20both%20the%20total%20force%20(column%20H)%20and%20the%20corresponding%20Actual%20Deflection(%20column%20I)%20have%20nonzero%20values.%20Is%20it%20doable%3F%20i%20believe%20this%20will%20avoid%20the%20error.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%2C%3CBR%20%2F%3ESam%3C%2FLINGO-BODY%3E
Contributor

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

10 Replies

@SamFares 

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,
Sam

@SamFares 

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
best response confirmed by SamFares (Contributor)
Solution

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.

@SamFares 

See the attached version.

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

@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.