Lookup help - multiple ranges

[gkerruish]

Hi there,

I am looking for a formula which can lookup the text in cell S7 and match this with the range of options in the range U3:U20. I would then need the formula to check the table range V3:AG20 for the cell which contains the percentage figure and then return the date in row 2 into cell T7.

I am effectively looking to return the month in which the rate increase kicks in, depending on which framework abbreviation is being referred to in cell S7.

I previously had this working using the following formula: =IFERROR(INDEX('Overall Assumptions (2)'!$W$2:$AH$2,MATCH(9.99999999999999E+307,INDEX('Overall Assumptions (2)'!$W$3:$AH$20,MATCH(S7,'Overall Assumptions (2)'!$V$3:$V$20,0),))),"")

However, this formula does not work if I am linking cells V3:AG20 to an underlying file with the same table (as the formula takes the cell links as values which it tries to calculate. There will only be one % uplift per framework at a time, so I was looking for a way around this, for the formula to only recognise numbers in the table.

Any help would be greatly appreciated.

Thank you,

 

[Fluff]

What is the formula that links the sheets?

 

[gkerruish]

What is the formula that links the sheets?

There was no formula linking the sheets - I had the underlying file open (which is exactly the same as the screenshot above) and just had cells W3:AH20 referencing the "master copy".

The background on this is because I have 21 models to update, so if I can get the assumptions tab in each model referencing one document, if I change a % value, it will update in each of the 21 models. However, when I done this, as each blank cell was returning 0%, it skewed the formula as it is currently searching for a cell with a number in it. I just can't work out how to get it to return the % value > 0 for each framework.

Hope this makes sense.

Thank you

 

[Fluff]

had cells W3:AH20 referencing the "master copy".

How?

 

[gkerruish]

How?

Clicking in cell W3 in the current file, pressing "=" and then clicking in the cell W3 on the master file.

 

[Fluff]

Ok, how about

=TAKE(SORTBY($W$2:$AH$2,FILTER($W$3:$AH$20,$V$3:$V$20=S7),-1),,1)

 

[gkerruish]

Ok, how about

=TAKE(SORTBY($W$2:$AH$2,FILTER($W$3:$AH$20,$V$3:$V$20=S7),-1),,1)

My word.. I thought I was okay at Excel until now. Thank you for your time and solution - it works like a dream!

 

[Fluff]

You're welcome & thanks for the feedback.