Index and If formula together with two criteria?

Excel43

New Member
Joined
Jan 7, 2020
Messages
13
Office Version
365, 2019
Platform
Windows, MacOS
Hi,

I would like to know how I can make this formula to use two criteria:

VBA Code:
=IFERROR(INDEX(Vikta;SMALL(IF(Vikta[Grupper]=1;ROW(Vikta)-4);ROW(2:2));1);"")

I have tried this but it gives me a a "#SPILL" error:

VBA Code:
=IFERROR(INDEX(Vikta;SMALL(IF(AND(Vikta[Grupper]=1;Vikta[Grupp]=I3);RAD(Vikta)-4);RAD(1:1));1);"")
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Excel43

New Member
Joined
Jan 7, 2020
Messages
13
Office Version
365, 2019
Platform
Windows, MacOS
Ooops, the last one should say "ROW" instead of "RAD". Translating it from Swedish
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,414
Office Version
365
Platform
Windows
Maybe something like this where you nest two IF functions instead of the AND.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

IFERROR(INDEX(Vikta;SMALL(IF(Vikta[Grupper]=1;IF(Vikta[Grupp]=I3;RAD(Vikta)-4));RAD(1:1));1);"")
 

Excel43

New Member
Joined
Jan 7, 2020
Messages
13
Office Version
365, 2019
Platform
Windows, MacOS
Thanks AhoyNC! It works great! I change the code a bit and would now need to nest the function with similar ones. Do you know if it is possible to nest it? I tried this, but it gives me errors:

VBA Code:
=INDEX(Vikta;SMALL(IF(Vikta[Grupper]=1;IF(Vikta[Grupp]=I3;ROW(Vikta)-4));ROW($1:$1));27;INDEX(Vikta;SMALL(IF(Vikta[Grupper]=2;IF(Vikta[Grupp]=I3;ROW(Vikta)-4));ROW($1:$1));27))
 

Excel43

New Member
Joined
Jan 7, 2020
Messages
13
Office Version
365, 2019
Platform
Windows, MacOS
I solved it. Did it this way:
VBA Code:
=INDEX(Vikta;MINSTA(OM(Vikta[Grupp]=I3;OM(Vikta[Grupper]=1;RAD(Vikta)-4);OM(Vikta[Grupper]=2;RAD(Vikta)-4));RAD($1:$1));27)
 

Watch MrExcel Video

Forum statistics

Threads
1,090,452
Messages
5,414,620
Members
403,536
Latest member
JEduardo

This Week's Hot Topics

Top