• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF Statements, Multiple Criteria

Ivin

New Member
Hello,
I've been trying to get my head around, where the "logical" broken link is within the spreadsheet.
Purpose behind it is depending on the List code will determine the formula's behaviour, whether it'll be charged at London rate, or Non-London rate, depending upon the dates and at which point the dates sit.

I can get the formula to work for one or the other separately but trying to combined them is causing a N/A error. What am I not seeing?

=IF(MATCH($V$1,T17:T22,0),IF(AND(L11>=$E$1,L11<=$F$1),VLOOKUP(I11,Table2016L,2,0),IF(AND(L11>=$E$2,L11<=$F$2),VLOOKUP(I11,Table2017L,2,0),IF(AND(L11>=$E$3,L11<=$F$3),VLOOKUP(I11,Table2018L,2,0),IF(AND(L11>=$E$4,L11<=$F$4),VLOOKUP(I11,Table2019L,2,0),IF(AND(L11>=$E$5,L11<=$F$5),VLOOKUP(I11,Table2020L,2,0),IF(AND(L11>=$E$6,L11<=$F$6),VLOOKUP(I11,Table2021L,2,0),

IF(MATCH($V$1,U17:U22,0),IF(AND(L11>=$E$1,L11<=$F$1),VLOOKUP(I11,Table2016L,4,0),IF(AND(L11>=$E$2,L11<=$F$2),VLOOKUP(I11,Table2017L,4,0),IF(AND(L11>=$E$3,L11<=$F$3),VLOOKUP(I11,Table2018L,4,0),IF(AND(L11>=$E$4,L11<=$F$4),VLOOKUP(I11,Table2019L,4,0),IF(AND(L11>=$E$5,L11<=$F$5),VLOOKUP(I11,Table2020L,4,0),IF(AND(L11>=$E$6,L11<=$F$6),VLOOKUP(I11,Table2021L,4,0),))))))))))))))

The first part is to calculate the rate for Non-London within the tables/date dependant and will calculate only if the ID number is within the Non-London list,(column T). It'll also work for column U with the London rates but unable to combined the pair.

Much appreciate.
 

Attachments

  • Sample.xlsx
    698.7 KB · Views: 8
Last edited:
Hi

You are making it way to complicated.

if you make a table of reference prices per year and they all have the same layout and values in "brand" and the "regions" in the column headers, then you can do this with 1 index formula with the "reference" style.

So I converted the ranges to tables and gave them a name "t_20xx". And made a table with the start dates to find my area number, this is the position of the year in the reference ranges.

=INDEX( (t_2016[#All],t_2017[#All],t_2018[#All],t_2019[#All],t_2020[#All],t2_2021[#All]), MATCH(Band,t_2016[Banding],0),MATCH(Region,t_2016[#Headers],0),MATCH(Date;tYrs[Date]) )

Please see attached file.
 

Attachments

  • Copy of Sample_nested_index_reference.xlsx
    694 KB · Views: 9
GraH, thank you. I did attempt a Index, match, match but were unable to get it to work. This is outstanding, I've got some excel formula learning to do.

When populating any cell within Column K with Band.1.A, this populates with Non London, or London, all others are functioning perfectly.
Much appreciated GraH.
 
Last edited:
GraH, thank you. I did attempt a Index, match, match but were unable to get it to work. This is outstanding, I've got some excel formula learning to do.

When populating any cell within Column K with Band.1.A, this populates with Non London, or London, all others are functioning perfectly.
Much appreciated GraH.
Sorry for intervene, I believe that you should change in formula from MATCH(Band,t_2016[Banding],0) to MATCH(Band,t_2016[Banding],0)+1, because index done with table including headers and match formula is not counting headers, so add 1 in that formula. actually not only Band 1.a. but all result is one row above to correct result.
 

Attachments

  • Copy of Sample_nested_index_reference (1).xlsx
    694.7 KB · Views: 6
Last edited:
Thanks for the feedback. Appreciated. And you are welcome. Enjoy the learning. Index is amazing.
Thanks for your solutions, i have not used Index Function along with [Area_Num] this was first time for me, still dont know basic of that, some how i guessed it referncing to year but how it is linking year number with table name that i will search for some example or tutorials of same. if you have any tutorial please share with me.
 
Another formula option,

1] Change range F1:F6 into your "Table Name" >> Table2016L, Table2017L…...Table2021L

2] In criteria V1, enter odd number for Non London, or even number for London

Then,

3] In G11, formula copied down :

=IFERROR(VLOOKUP($I11,INDIRECT(LOOKUP($L11,$E$1:$E$6,$F$1:$F$6),0),2+ISEVEN($V$1)*2,0),"")

or, this shorter :

=IFERROR(VLOOKUP($I11,INDIRECT(LOOKUP($L11,$E$1:$F$6),0),2+ISEVEN($V$1)*2,0),"")

75057
 

Attachments

  • MultiTableLookup(BY).xlsx
    687.4 KB · Views: 4
Last edited:
Sorry for intervene, I believe that you should change in formula from MATCH(Band,t_2016[Banding],0) to MATCH(Band,t_2016[Banding],0)+1, because index done with table including headers and match formula is not counting headers, so add 1 in that formula. actually not only Band 1.a. but all result is one row above to correct result.
Verry good catch :).
But easier, is like so =INDEX((t_2016;t_2017;t_2018;t_2019;t_2020;t2_2021);MATCH(K11;t_2016[Banding];0);MATCH(M11;t_2016[#Headers];0);MATCH(L11;tYrs[Date])).
The [#All] was added by selecting the tables with the mouse, I did not pay attention to it. But I should have. So thanks for correcting.
Thanks for your solutions, i have not used Index Function along with [Area_Num] this was first time for me, still dont know basic of that, some how i guessed it referncing to year but how it is linking year number with table name that i will search for some example or tutorials of same. if you have any tutorial please share with me.
Simply look in the help. A great resource to learn formula syntax.
 
Back
Top