Nested IF(ISNA(Vlookup or ?

[stapuff]

I read a post that doing multiple Vlookup's is not a very efficient way to find a value in multiple places.


B2 is where I want the result of up to 3 different ranges

This is not written correctly. I just wanted to illustrate the ranges.
Vlookup,A2,F2:G200,2,False
Vlookup,A2,M2:O250,2,False
Vlookup,A2,X2:Z150,3,False

I have been able to get a double lookup to work but not a third.

What is a better way to do multiple range lookups or how could I add the 3 Vlookup.

At home right now so I can't post the IF(ISNA(VLOOKUP statement that I already have.

Thanks,


Kurt

 

[Yogi Anand]

Hi Kurt:

It will be nice if you can post a few rows of your sample data showing your data layout and expected results -- so we can clearly see what you are working with -- and then le tus take it from there.

 

[stapuff]

Yogi -

I am not allowed to use Colo at work and I did not think of sending the Wb home.

The just of it is a follows:

A2:A150 is a list of our customer part #'s(open orders). B2:B150 will be a list of our part #'s that are tied to our customer's. Parent/Child type of relationship.

Each of the 3 vlookup ranges are different commodities.

Range 1 is fully machined aluminum castings
Range 2 is cast (Only) aluminum castings
Range 3 is components that are assembled onto the castings

Each one of the ranges is downloaded from our AS-400 system.

I thought I had the thing licked until range 3 came into play.

A2 will be 1 of the 3 ranges and only 1.

Thanks,

Kurt

 

[Yogi Anand]

Hi Kurt:

Let us see if I have captured your intent correctly ...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1						#N/A							#N/A											5
2	X_4	Y_4				MachinedAL1	MachinedAL2						CastAL1	CastAL2	CastAL3									Assemb1	Assemb2	Assemb3
3						F_2	G_2						M_2	N_2	O_2									X_2	Y_2	Z_2
4						F_3	G_3						M_3	N_3	O_3									X_3	Y_3	Z_3
5						F_4	G_4						M_4	N_4	O_4									X_4	Y_4	Z_4
6													M_5	N_5	O_5									X_5	Y_5	Z_5
7													M_6	N_6	O_6
8
Sheet11

A2 houses the entry to be Looked up in the Table F2:Z7

array formula in cell F1 is ... =MATCH(TRUE,$A2=F3:F7,0)+ROW($2:$2)

this is then copied to cells M1 and X1

then finally the formula in cell B2 is ... =INDEX(A1:Z7,INDEX(1:1,MATCH(26,1:1)),MATCH(26,1:1)+1)

Would this do?

 

[Aladin Akyurek]

I read a post that doing multiple Vlookup's is not a very efficient way to find a value in multiple places.


B2 is where I want the result of up to 3 different ranges

This is not written correctly. I just wanted to illustrate the ranges.
Vlookup,A2,F2:G200,2,False
Vlookup,A2,M2:O250,2,False
Vlookup,A2,X2:Z150,3,False

I have been able to get a double lookup to work but not a third.

What is a better way to do multiple range lookups or how could I add the 3 Vlookup.

At home right now so I can't post the IF(ISNA(VLOOKUP statement that I already have.

Thanks,


Kurt

Multi-cell approach...

D2:

=VLOOKUP(A2,$F$2:$G$200,2,0)

C2:

=IF(ISNA(D2),VLOOKUP(A2,$M$2:$O$250,2,0),D2)

B2, which is the final result cell:

=IF(ISNA(C2),VLOOKUP(A2,$X$2:$Z$150,3,0),C2)

If the lookup tables are sorted in ascending order on their first columns, a more efficient set up is also possible.

 

[stapuff]

Yogi -

I am still trying to digest your post.

1 question though:

What if X4 is in more than 1 range?


Could a nested IF(Findoffset be a possibility?

Thanks,

Kurt

 

[Yogi Anand]

Yogi -

I am still trying to digest your post.

1 question though:

What if X4 is in more than 1 range?

<font color="blue">My formula will pick up from the first occurance of X_4 going left to right. I don't know your project requirements or constraints, however I believe you will not have have ovelapping designations for different material specs or assemblies</font>

Could a nested IF(Findoffset be a possibility?

<font color="blue">Sorry, without knowing what your setup with identical or overlapping designations might look like, I can't say. If you do have a formula in mind , how about if you post your formula with the corresponding data -- and then let us take it from there.</font>

 

[stapuff]

Yogi -


Nest IF - Something like this:

=IF(FindOffset(Product_Structures!X2:X200,A17,1)=0,FindOffset(Product_Structures!AE2:AE200,A17,1),(IF(FindOffset(Product_Structures!AE2:AE200,A17,1)=0,(FindOffset(Product_Structures!J2:J200,A17,1),(IF(FindOffset(Product_Structures!J2:J200,A17,1)=0,0,FindOffset(Product_Structures!X2:X200,A17,1)))))))

This is the last of many variations. I tried =0, =false, etc. I keep getting N/A

Thanks,

Kurt

 

[stapuff]

In addition a quick update. I have gotten this down to 2 cells required.

In B4 I have:
=IF(ISNA(VLOOKUP(A4,Product_Structures!$X$2:$AB$1000,2,FALSE)),VLOOKUP(A4,Product_Structures!$AE$2:$AI$1000,2,FALSE),VLOOKUP(A4,Product_Structures!$X$2:$AB$1000,2,FALSE))

In C4 I have:
=IF(ISNA(B4),VLOOKUP(A4,Product_Structures!$J$2:$N$200,2,FALSE),(B4))

Thank You Aladin for the suggestion.

My intent is getting it down to only 1 cell being needed however I can live with the extra cells if required.

Thanks,

Kurt

 

[Yogi Anand]

In addition a quick update. I have gotten this down to 2 cells required.

In B4 I have:
=IF(ISNA(VLOOKUP(A4,Product_Structures!$X$2:$AB$1000,2,FALSE)),VLOOKUP(A4,Product_Structures!$AE$2:$AI$1000,2,FALSE),VLOOKUP(A4,Product_Structures!$X$2:$AB$1000,2,FALSE))

In C4 I have:
=IF(ISNA(B4),VLOOKUP(A4,Product_Structures!$J$2:$N$200,2,FALSE),(B4))

Thank You Aladin for the suggestion.

My intent is getting it down to only 1 cell being needed however I can live with the extra cells if required.

Thanks,

Kurt

Hi Kurt:

Here is a one cell solution ...

y041110h1.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2	X_4	Y_4				MachinedAL1	MachinedAL2						CastAL1	CastAL2	CastAL3									Assemb1	Assemb2	Assemb3
3						F_2	G_2						M_2	N_2	O_2									X_2	Y_2	Z_2
4						F_3	G_3						M_3	N_3	O_3									X_3	Y_3	Z_3
5						F_4	G_4						M_4	N_4	O_4									X_4	Y_4	Z_4
6													M_5	N_5	O_5									X_5	Y_5	Z_5
7													M_6	N_6	O_6
8
Sheet11 (2)

formula in cell B2 is ...

=IF(ISNA(VLOOKUP(A2,F2:G7,2,0)),IF(ISNA(VLOOKUP(A2,M2:O7,2,0)),IF(ISNA(VLOOKUP(A2,X2:Z7,2,0)),NA(),VLOOKUP(A2,X2:Z7,2,0)),VLOOKUP(A2,M2:O7,2,0)),VLOOKUP(A2,F2:G7,2,0))

As you can see it does get quite unnwieldy -- and if you have to incorporate more choices, it becomes even more cumbersome.