IF and LOOKUP with multiple nested IF functions

[hydraulicwave]

Hi there,

I'd like some help on another alternative to this:

=IF(AZ9<=2000,LOOKUP(BB9,AG3:AG24,AF3:AF24),IF(AZ9<=2005,LOOKUP(BB9,AI3:AI24,AH3:AH24)))

Cell AZ9 can vary from 2000 to 10000

How can I do this is in increments of 5 without trying to nest 1600 IF functions?

Many thanks,

[AliGW]

Create a VLOOKUP table.

[hydraulicwave]

Hi,
I'm not sure if that will work.
The lookup vector and result vector change dependent on the value of AZ9. So, basically the lookup vector and result vector move along at every increment of 5 from 2000 to 10000 as per the value of AZ9 as per below

2000 2005 2010
Density (kg/m3) Entropy (J/g*K) Density (kg/m3)
369.94 1.5999 414.53 1.6028 447.72 1.6016
365.34 1.6124 411.23 1.6125 445.04 1.6101
360.83 1.6248 407.98 1.6222 442.38 1.6185
356.42 1.637 404.76 1.6318 439.75 1.6268
352.09 1.649 401.57 1.6413 437.13 1.635
347.86 1.6609 398.43 1.6507 434.55 1.6432
343.71 1.6726 395.33 1.6601 431.98 1.6514
339.66 1.6842 392.26 1.6693 429.44 1.6595
335.69 1.6956 389.23 1.6785 426.92 1.6675
331.81 1.7068 386.25 1.6876 424.43 1.6754
328.02 1.7179 383.3 1.6966 421.96 1.6833
324.31 1.7289 380.39 1.7055 419.51 1.6912
320.68 1.7397 377.51 1.7143 417.08 1.699
317.13 1.7503 374.68 1.7231 414.68 1.7067
313.66 1.7608 371.88 1.7317 412.3 1.7144

[AliGW]

Please do us a favour and attach a workbook.

[ragulduy]

I think maybe something like:
=LOOKUP(BB9,OFFSET(AG3:AG24,0,ROUND(0.4*AZ9-800.8-0.4*MOD(AZ9,5)+0.8,0)),OFFSET(AF3:AF24,0,ROUND(0.4*AZ9-800.8-0.4*MOD(AZ9,5)+0.8,0)))

Edit, just seen your post - if you have the headings 2005/2010/2015 etc above the columns it would be easier to use match on that row to find which column to look at than the curve-fitting approach I used to get the right column.

[hydraulicwave]

I think maybe something like:
=LOOKUP(BB9,OFFSET(AG3:AG24,0,ROUND(0.4*AZ9-800.8-0.4*MOD(AZ9,5)+0.8,0)),OFFSET(AF3:AF24,0,ROUND(0.4*AZ9-800.8-0.4*MOD(AZ9,5)+0.8,0)))

Edit, just seen your post - if you have the headings 2005/2010/2015 etc above the columns it would be easier to use match on that row to find which column to look at than the curve-fitting approach I used to get the right column.

How will that work?
I understand that the match function could find the column, but how do you get it to look at the correct lookup vector and result vector based on that?
thank you

[ragulduy]

Well in the formula I posted, I use

ROUND(0.4*AZ9-800.8-0.4*MOD(AZ9,5)+0.8,0)

to return how many columns to move across from AG as a reference, so that bit of the formula will return 0 for 2000-2004, 2 for 2005-2009, 4 for 2010-2014 etc.

If you already have the headings however, you can replace that bit of the formula with a match function to return the offset for the column.

[hydraulicwave]

I've attached a workbook.
My main questions is how do I reduce the need for so many if functions if I have a range of 2000 - 10000 in increments of 5?
Thanks

[hydraulicwave]

Well in the formula I posted, I use

ROUND(0.4*AZ9-800.8-0.4*MOD(AZ9,5)+0.8,0)

to return how many columns to move across from AG as a reference, so that bit of the formula will return 0 for 2000-2004, 2 for 2005-2009, 4 for 2010-2014 etc.

If you already have the headings however, you can replace that bit of the formula with a match function to return the offset for the column.

Thank you. both work nicely.

[oeldere]

Don't worl with merged cells, you get in trouble with it sooner or later.

Maybe like this

f3 =d3+5 and drag to the right.

See the attached file.