nesting more than 7 if functions-possible?

ngc2392

Board Regular
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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

KenWright

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

fairwinds

MrExcel MVP
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
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
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
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
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?

Oops - You'll need to adjust for sheet references

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
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.

ngc2392

Board Regular
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
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

Replies
4
Views
690
Replies
4
Views
158
Replies
2
Views
392
Replies
3
Views
648
Replies
2
Views
384

1,181,821
Messages
5,932,258
Members
436,830
Latest member
Sochen

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?

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

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