nesting more than 7 if functions-possible?

ngc2392

Board Regular
Joined
Oct 24, 2002
Messages
54
hello guys...

need help with if functions. is there a way around about restriction of 7 if functions in one function? i have 9 if situations and now i run out of options :) is there maybe a vba solution? problem is quite easy...if a=1 vlookup that range, if=2 vlookup another range (to simplify). and that up to 7 times. thanks in advance...
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

KenWright

Active Member
Joined
Jan 14, 2005
Messages
267
Post the formula you currently have, albeit it doesn't work. It gives us an idea of your ranges and intent.
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

You could try:

If your vlookups returns text:

=LOOKUP(REPT("z",255),CHOOSE({1,2,3,4,5,6,7,8,9},Vlookup9,Vlookup8,Vlookup7,Vlookup6,Vlookup5,Vlookup4,Vlookup3,Vlookup2,Vlookup1))


If they returns numbers:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3,4,5,6,7,8,9},Vlookup9,Vlookup8,Vlookup7,Vlookup6,Vlookup5,Vlookup4,Vlookup3,Vlookup2,Vlookup1))
 

bobmc

Board Regular
Joined
Mar 13, 2002
Messages
142
You may try:

=VLOOKUP(MATCH(A1,D1:D5,0),D1:E5,2,FALSE)

for the example below...
Book1
ABCDE
1330110
2220
3330
4440
5550
Sheet1
 

ngc2392

Board Regular
Joined
Oct 24, 2002
Messages
54

ADVERTISEMENT

well, ken..........here u have it :)
+IF(H11="B";VLOOKUP(J11;OPIS!$A$10:$B$60;2);IF(H11="C";VLOOKUP(J11;OPIS!$D$10:$E$60;2);IF(H11="D";VLOOKUP(J11;OPIS!$G$10:$H$60;2);IF(H11="F";VLOOKUP(J11;OPIS!$J$10:$K$60;2);IF(H11="G";VLOOKUP(J11;OPIS!$M$10:$N$60;2);IF(H11="M";VLOOKUP(J11;OPIS!$P$10:$Q$60;2);IF(H11="N";VLOOKUP(J11;OPIS!$S$10:$T$60;2))))))))

when i want to put another if...it gives me error. that's it...

i'll look into that match thing...if i can :)

i think match isn't the proper thing.....as i tested.

thanks!
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
So I guessed wrong...


Try building on this instead;

=VLOOKUP(J11,CHOOSE(MATCH(H11,{"B","C","D","F","G","M","N"},0),OPIS!$A$10:$B$60,OPIS!$D$10:$E$60,OPIS!$G$10:$H$60,OPIS!$J$10:$K$60,OPIS!$M$10:$N$60,OPIS!$P$10:$Q$60,OPIS!$S$10:$T$60),2)


EDIT:

To make it work for you, you need semicolons:

=VLOOKUP(J11;CHOOSE(MATCH(H11;{"B";"C";"D";"F";"G";"M";"N"};0);OPIS!$A$10:$B$60;OPIS!$D$10:$E$60;OPIS!$G$10:$H$60;OPIS!$J$10:$K$60;OPIS!$M$10:$N$60;OPIS!$P$10:$Q$60;OPIS!$S$10:$T$60);2)
 

KenWright

Active Member
Joined
Jan 14, 2005
Messages
267

ADVERTISEMENT

That's the pattern i was looking for :)

=VLOOKUP(J11,(OFFSET(A1,,((CODE(UPPER(H11))-66)/2)*3,60,2)),2)

You may need to switch commas for semi colons

You're looking for nearest match right, and not an exact match, OR, your table data is sorted in ascending order - yes?

Addendum
Oops - You'll need to adjust for sheet references

Addendum2
What the hell:-

=VLOOKUP(J11,(OFFSET(OPIS!A1,,((CODE(UPPER(H11))-66)/2)*3,60,2)),2,0)

Lose the last argument of 0 that i added if you really don't need it
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi Ken! Smart! but I'm not sure... I cannot see that he has E, H and I

But maybe those are the ones that needs to be added. :biggrin:
 

ngc2392

Board Regular
Joined
Oct 24, 2002
Messages
54
fairwinds....it doesn't work somehow. when i put also missing 2 choices in formula, it gives me wrong name within formula? also...i changed brackets form yours to mine []. also nothing.
 

ngc2392

Board Regular
Joined
Oct 24, 2002
Messages
54
ken...and fairwinds...e, h are colums within vlookup search strings...uh..i try the ken's formula (to be honest...i don't even know what it does :)
 

Forum statistics

Threads
1,147,846
Messages
5,743,521
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top