Easier formula for nestled if statement

[Paigan]

Hello everybody,

I am trying to find an easier way to look up values out of Database sheet if certain criteria are met.

{=IF(A2=1;INDEX('Source Sheet'!$B$2:$B$18;(MATCH(2;'Source Sheet'!$A$2:$A$18;0)));IF(A2=2;(INDEX('Source Sheet'!$C$2:$C$18;MATCH(3;'Source Sheet'!$A$2:$A$18;0)));IF(A2=3;(INDEX('Source Sheet'!$D$2:$D$18;MATCH(4;'Source Sheet'!$A$2:$A$18;0)));IF(A2=4;(INDEX('Source Sheet'!$E$2:$E$18;MATCH(4;'Source Sheet'!$A$2:$A$18;0)));IF(A2=5;(INDEX('Source Sheet'!$F$2:$F$18;MATCH(4;'Source Sheet'!$A$2:$A$18;0)));0)))))}

My boss is convinced there is a formula built in to excel. Before I tell him a defenite "No" I want to make really sure there is no simpler way.

I am aware I could define a custom function within VBA to do different lookups for different criteria (e.g. if there are more than 7) to make it look simpler and if need be I would create one.

For reference, my workbook simplified looks something like this:
Destinationsheet:

Criteria	Lookup for Criteria
1	nestled if statement
2	nestled if statement
3	nestled if statement
4	nestled if statement
5	nestled if statement

<tbody>
</tbody>

Sourcesheet:

uuu	vvv	www	xxx	yyy	zzz	aaa
1	a	b	c	d	e	abc
2	a	b	c	d	e	bcd
3	a	b	c	d	e	cde
4	a	b	c	d	e	abc
5	a	b	c	tra	e	bcd
6	a	b	c	d	e	cde
7	a	b	c	d	e	abc
8	a	b	c	d	e	bcd
9	a	b	c	d	e	cde
5	a	b	c	d	e	abc
11	a	b	c	d	e	bcd
12	a	b	c	d	e	cde
13	a	b	asdf	d	e	abc
1	a	b	c	d	e	bcd

<tbody>
</tbody>

Does anyone know of a way to do this in an easier way and for more than 7 criteria without using VBA?

Best

Paigan

 

[Eric Golf]

Hi,

How about creating a table for the column to return for each Criteria e.g. in Y1:Z7

Criteria Column
1 1
2 2
3 3
4 4
abc 12
xyz 27

then you can nest Vlookups as below

=VLOOKUP($A2,'Source Sheet'!$A$2:$F$18,VLOOKUP($A2,$Y$1:$Z$7,2,0),0)

again, untested.

Eric.

 

[Paigan]

Hey Eric,

thanks for the workaround.
Yes that one work very well! The only "problem" is the helper table. It is supposed to do everything in one cell.
I will try and write a custom formula to do what I need. Will post my final solution here.
Maybe It will help somebody else solve a similar problem .

Regards and thanks everybody for the help and time!

Paigan