Return a number based on Criteria

[David Jenkins]

Hi there,

I have attached a file with an example.

Basically, I am going to be looking up a number that will be in a hidden row (its not hidden in the example - but that wont cause an issue).

I want it to return the number based on the last cell in a row below with a value in it that doesn't equal ~ or - (these are the results of no data from a formula looking for data that isnt there).

Thank you for any assistance offered.

Dave

 

[Hui]

Like: =INDEX(C3:Z3,0,MATCH("~",C6:Z6,-1)-1)

 

[Luke M]

This will find the highlighted value:
=INDEX(3:3,MATCH(9E+99,6:6))

 

[David Jenkins]

Thanks both!

Hi Hui, although yours would return the desired result in the current situation it could also be a - sign so wouldn't return in that situation.

Hi Luke, it works perfectly. I understand most of the formula that you have provided except this bit: 9E+99 - what is it matching against?

Thank you again.

Regards,

Dave.

 

[David Jenkins]

Actually, I have just tried implementing this through a linked workbook and its just returning a 0 rather than a number. I had to make an amendment because the sheet that the formula will be in is a file format of 97-2003.

so the formula reads:

{=INDEX('[KPI Grid V5k.xlsm]Monthly'!$A$3:$IV$3,MATCH(9E+99,'[KPI Grid V5k.xlsm]Monthly'!$A$6:$IV$6))}

Does this formula work across linked sheets or would it only work in the same sheet?

Regards,

Dave

 

[Luke M]

It should work across linked sheets...is there something different in the layout in your actual data?

The MATCH function has an optional 3rd argument, saying to look for either exact match or closest match equal to or less than. In our case, we are wanting the last number in a range. In our formula, we told that MATCH that we want to find the closest value, and it assumes our data is sorted (although it really isn't). So, I just tell it to look for some randomly large number, and it goes to the last number in our range, and says "Well, I didn't find any number equal to or greater than 9*10^99, so this last number is the best I can do.

 

[David Jenkins]

Hi Luke!

Thank you for your help.

Without thinking about, there was a change to the format. Basically, an additional two columns were added to the end for targets. I have just slightly amended the formula to cut off before these columns and it works fine.

Thank you for the explanation above as well. I like to understand whats happening so hopefully one day I'll be good enough to come up with complex formulas

Regards,

Dave.