IFerror index problems, Im stuck

DonBDK

New Member
Joined
Dec 29, 2016
Messages
12
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;"")
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

AliGW

.
Joined
Mar 9, 2014
Messages
3,628
There is probably an easier way. How is your data laid out? Where do your results need to appear?
 

DonBDK

New Member
Joined
Dec 29, 2016
Messages
12
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.
 

AliGW

.
Joined
Mar 9, 2014
Messages
3,628
Please provide a visual - preferably an editable table. Grab the ForumTools add-in (link in my signature line) to do this.
 

DonBDK

New Member
Joined
Dec 29, 2016
Messages
12

ADVERTISEMENT

Sorry to say byt cant find the tool.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

DonBDK

New Member
Joined
Dec 29, 2016
Messages
12
=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

 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,653
Members
414,083
Latest member
Mrsash

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top