IFerror index problems, Im stuck

DonBDK

New Member
Hi I use this formula to calculate losses in weight and it works fine:
=IFERROR(INDEX(C10:C62;MATCH(9^9;C10:C62))-C6;"")
When I use the C10:c62 it works fine.

The problem is that i also want to use 6 cells not in a row, like C10, C14, C16, C20, C22, C24
I just cant get the formula to work.
=IFERROR(INDEX(C10;C14;C16;C20;C22;C24;MATCH(9^9;C10;C14;C16;C20;C22;C24))-C6;"")

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
There is probably an easier way. How is your data laid out? Where do your results need to appear?

Hi AliGW, thanks for feedback.

In Cell C1 I have the result.

In Cell C4 I got my 1 measurment lets says 2 CM its my reference.
In Cell C8 it got my 2 measurment 3 CM Then i Want the Cell C1 to display 1 CM (C4+C8)
In Cell C13 it got my 3 measurment 4 CM Then i Want the Cell C1 to display 2 CM (C4-C13)
In Cell C 16 I maybe then have 3 CM and I then want C1 ti display 1 cm.

I need it always to take the newest measurment and substract from C4 and display in C1

Then when I do a new measurment and put it into Cell C12 = 2 cm, I want Cell C1 to show the diffence between the newest input and the reference.

Please provide a visual - preferably an editable table. Grab the ForumTools add-in (link in my signature line) to do this.

Sorry to say byt cant find the tool.

Hi I use this formula to calculate losses in weight and it works fine:
=IFERROR(INDEX(C10:C62;MATCH(9^9;C10:C62))-C6;"")
When I use the C10:c62 it works fine.

Are you fishing for the last numeric value? If so...

=LOOKUP(9.99999999999999E+307;C10:C62)

The problem is that i also want to use 6 cells not in a row, like C10, C14, C16, C20, C22, C24
I just cant get the formula to work.
=IFERROR(INDEX(C10;C14;C16;C20;C22;C24;MATCH(9^9;C10;C14;C16;C20;C22;C24))-C6;"")

Possibly...

=IFERROR(LOOKUP(9,99999999999999E+307;CHOOSE(ROW(INDIRECT("1:"&COUNT(C10;C14;C16;C20;C22;C24)));C10;C14;C16;C20;C22;C24))-C6,"")

This assumes a comma for decimals and a semi-colon as list separator.

Thanks Aladin, but it only work with the first cell.

Thanks Aladin, but it only work with the first cell.

Given C6:C24 below, what is the expected result?

 80 82 83 81 83 84 83

<tbody>
</tbody>

=IFERROR(INDEX(C10:C62;MATCH(9^9;C10:C62))-C6;"") this works perfect.

The problems is when the cells C10:C16 is located like C10 C12 C16 C20 C22 C24 and not just under each other

Replies
2
Views
387
Replies
17
Views
1K
Replies
2
Views
556
Replies
13
Views
979
Replies
1
Views
2K

1,196,371
Messages
6,014,893
Members
441,854
Latest member
Amstaff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back