Numbers Formulas Help

Copy of reply to your other post containing this question.

The difficulty here is that there is no function that will do the search directly for a value that is "within ±10% of a target.

MATCH, which is the search functionI used here, is capable of finding the largest value less than or equal to the search value, or of finding the smallest value more than or equal to the search value, or of requiring an exact match of the search value.

A formula using this talent of MATCH, and adding INDEX to return the matching percentage, and a table containing two auxiliary columns to show the upper and lower limits of the values acceptable to meet the ±10% requirement can be constructed as shown in the example below:

The formula is entered as shown in cell B2 of Table 2, then filled down to the last cell in that column.

IFERROR(IF(INDEX(Table 1::A,MATCH(A2,Table 1::C,1),)=INDEX(Table 1::A,MATCH(A2,Table 1::D,−1)),INDEX(Table 1::A,MATCH(A2,Table 1::B,1),),""),"n/a")

The formula uses this pair of functions, MATCH and INDEX to look up a percentage in Table 1 twice to determine if there is a match that is within the ±10% range. If the first search (of column C for a number less than or equal to the value being searched) and the second (of column D for a number greater than or equal to the value being searched) both return the same row number (and consequently the same percentage value, IF repeats the first search and returns the value it finds.

If the two Match and INDEX searches return different values, the comaparison returns FALSE, and IF returns a null string, making the cell containing the formula appear blank.

The whole formula (bold part) returns an error if either MATCH returns a 'can't Find error. This error message is caught by IFERROR, which returns the "n/a" text string seen in the bottom rows of column B of Table 2.

I would leave this (not bolded) part out while constructing and testing the formula (bold part) until you have that part error free (except fo the expected error which will occur in the cells showing "n/a" in the example.

You can find details regarding the three functions used in the Function Browser.

Regards,

Barry

You may want to rethink this a bit.

+/-10% ends up with a whole lot of overlap.

+/-10% of your lowest number 70 is 63 to 77

+/-10% of the next number 80 is 72 to 88

Any match from 72-77 will appear on both rows

+/-10% of your highest number 310 = 279 to 341

This overlaps several rows, which all also overlap each other.

The middles rows overlap in both directions. They are the worst.

Maybe you want +/-5 instead, as in (A-5)<=match<(A+5)

Add a new column to Table 1 with the formula =ROUND(B,-1)

This will round your values to the nearest ten dollars. Now you can use XLOOKUP from Table 2

=XLOOKUP(A,Table 1::C,Table 1::A,"",0)

Note that if two rows in Table 1 round to the same number, one is going to get dropped, it will not make it over to Table 2. If you want them both included, you can add the percentages together vs doing a lookup. Use SUMIF instead of XLOOKUP.

=SUMIF(Table 1::C,A,Table 1::A)

or, to blank out the zeros,

=IF(SUMIF(Table 1::C,A,Table 1::A)=0,"",SUMIF(Table 1::C,A,Table 1::A))

Hi Nick,

I am having trouble understanding your question. Please tell us your overall aim. Tell us "what" you want to do, instead of asking "how".

More information may lead to a solution 😉.

Regards,

Ian.

HI Nick,

"Thanks Barry, when I pasted the formula, it just gave me N/A. "

That's odd. I would expect it to give the same results as I got, unless you were using different values in columns. As can be seen in the images in my reply to your other statement of this question (here: Numbers Functions/Formulas Help - Apple Community ), the only "n/a" results I received were for values in column A of the second table greater than 237, the +10% value for 216, the largest test value liste in the first table.

The copy of that response in this discussion was placed here using copy — paste, and dropped the images I'd included with the original.

Regards,

Barry