Aug 01 2022 10:37 PM
Hi,
I write a formula and it shows #NUM! error
I have added an image and the link of the sample file below
Please have a look
Thank you
Aug 01 2022 10:55 PM
This function LARGE(K5:K38,34) causing error because you do not have 34th largest value in range K5:K38. What is you desired output from LARGE(K5:K38,34)?
Aug 02 2022 04:58 AM - edited Aug 02 2022 04:59 AM
because I want to get entire 34 rows of the table,and sorted values of column K from highest to lowest and using Filter function to get out 3 columns from 6 first columns, but I dont know what the function to get that.That's why I use it.
I writed a formula again:
=INDEX(SORT(I5:N38,3,-1),SEQUENCE(34),{1,2,3,4,5,6})
the value is sorted fail
Can you give me some advicing
Thank you
Aug 02 2022 05:28 AM - edited Aug 02 2022 05:28 AM
Solution@littlevillage The formula in column K returns a hyphen when the VLOOKUP doesn't find a match. Change that to the number 0 (no quotation marks). Then the formula in X5 will sort correctly.
By the way, since you have a modern Excel version that supports dynamic arrays, why not skip using VLOOKUP. Better to look into XLOOKUP. Add more flexibility and no need for IFERROR as XLOOKUP includes an argument [if_not_found].
Aug 02 2022 04:51 PM
Thank you Riny
It is done
How can the Xlookup returns array what I expect the same Index function
Aug 02 2022 09:13 PM
@littlevillage Nothing wrong with that index function. I was referring to the formulas in column K in the earlier schedule, where VLOOKUP could be replaced by XLOOKUP.
Aug 02 2022 05:28 AM - edited Aug 02 2022 05:28 AM
Solution@littlevillage The formula in column K returns a hyphen when the VLOOKUP doesn't find a match. Change that to the number 0 (no quotation marks). Then the formula in X5 will sort correctly.
By the way, since you have a modern Excel version that supports dynamic arrays, why not skip using VLOOKUP. Better to look into XLOOKUP. Add more flexibility and no need for IFERROR as XLOOKUP includes an argument [if_not_found].