nesting more than 7 if functions-possible?

[ngc2392]

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

 

[KenWright]

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

 

[fairwinds]

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]

You may try:

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

for the example below...

Book1
	A	B	C	D	E
1	3	30		1	10
2				2	20
3				3	30
4				4	40
5				5	50
Sheet1

 

[ngc2392]

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]

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]

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]

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]

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]

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