IF formula to ignore result if both INDEX functions return from the same cell?

**georgie_t** · 11-12-2021, 03:47 PM

I am trying to track change using formulas that are flexible enough to recognise the first and last cells with values in them, rather than picking particular columns. The trouble is, that when I want to use a formula to tell me what difference, if any, there is between the first and last values, sometimes they both come from the same column. When that is the case (as in the rows highlighted in yellow in my workbook), I want that to be clear without having to manually check.

I'm using LET and IFS, so I wonder if there's an IF function I can add that doesn't rely on values but rather the fact that both formulas track to the same cell.
It would basically be IF first and last come from/are in the same cell, then "text". Any help would be very gratefully received.

**Fluff13** · 11-12-2021, 03:59 PM

How about

Formula:

Please Login or Register  to view this content.

**georgie_t** · 11-15-2021, 05:46 AM

Thank you, I'm going to try this today!

**georgie_t** · 11-15-2021, 10:08 AM

Unfortunately I can't get this to work - Excel won't read First and Last as names in the calculations for Firsti and Lasti. I appreciate the suggestion though, thank you.

**Fluff13** · 11-15-2021, 10:36 AM

Not sure what you mean by "Excel won't read First and Last as names in the calculations for Firsti and Lasti."
Did you try the formula with the test data you supplied?

**georgie_t** · 11-15-2021, 12:05 PM

Apologies - I made an error in transferring the formula to my actual worksheet and had too many arguments in there. I'm still having a little trouble though; in one case in the attached file, the formula is returning 'gone up' when it should be 'gone down', and in another, the formula is returning an array instead of a single value. If you have time and don't mind, are you able to explain what's gone wrong? Thanks again.

**Fluff13** · 11-15-2021, 12:24 PM

That looks to be the same workbook you uploaded originally & doesn't have the formula I suggested, so cannot see what problems you are getting.

**georgie_t** · 11-15-2021, 01:28 PM

Sorry about that, I didn't realise I had saved it in two locations.

**Fluff13** · 11-15-2021, 02:31 PM

Thanks for that, how about

Formula:

Please Login or Register  to view this content.

**Fluff13** · 11-15-2021, 03:05 PM

A slightly shorter version

Formula:

Please Login or Register  to view this content.

**georgie_t** · 11-16-2021, 05:58 AM

This is brilliant, thank you so much. Ideally I'd like to tweak it so that if every entry says "Insufficient data" (as in Row 11, Bill), then this also returns "Insufficient data", rather than saying "No change". If you still have the patience, would you mind helping me with where I would insert that condition? I'm sorry to be a pain, I really am so thankful for your help.

**Fluff13** · 11-16-2021, 09:51 AM

How about

Formula:

Please Login or Register  to view this content.

**georgie_t** · 11-16-2021, 02:08 PM

Absolute magic, thank you a million times!

**Fluff13** · 11-16-2021, 02:11 PM

You're welcome & thanks for the feedback.

**georgie_t** · 11-17-2021, 05:30 AM

I'm so grateful for your expertise and your patience.

IF formula to ignore result if both INDEX functions return from the same cell?

LinkBack

Thread Tools

Rate This Thread

Display

IF formula to ignore result if both INDEX functions return from the same cell?

Re: IF formula to ignore result if both INDEX functions return from the same cell?

Re: IF formula to ignore result if both INDEX functions return from the same cell?

Re: IF formula to ignore result if both INDEX functions return from the same cell?

Re: IF formula to ignore result if both INDEX functions return from the same cell?

Re: IF formula to ignore result if both INDEX functions return from the same cell?

Re: IF formula to ignore result if both INDEX functions return from the same cell?

Re: IF formula to ignore result if both INDEX functions return from the same cell?

Re: IF formula to ignore result if both INDEX functions return from the same cell?

Re: IF formula to ignore result if both INDEX functions return from the same cell?

Re: IF formula to ignore result if both INDEX functions return from the same cell?

Re: IF formula to ignore result if both INDEX functions return from the same cell?

Re: IF formula to ignore result if both INDEX functions return from the same cell?

Re: IF formula to ignore result if both INDEX functions return from the same cell?

Re: IF formula to ignore result if both INDEX functions return from the same cell?

Thread Information

Users Browsing this Thread

Similar Threads

Hyperlink to INDEX Formula Result Cell

INDEX and AGGREGATE to ignore 0's and functions

[SOLVED] Ignore formula and return zero if cell is blank

[SOLVED] Cannot combine IF,AND, INDEX functions to produce a clean result

[SOLVED] An INDEX MATCH formula has blank cell - need to ignore or delete

Advanced formula - Return result & Show Cell Reference of result

[SOLVED] using muliple functions to return one result

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions