Result from sub-cat from category using recursion

[kevincarbonaro]

Hi,

I am trying to create a recursive formula to get results (in red) as below.

On the left is a list of categories/sub-cats/results. From this list I want to match category 'a' [cell F2] and each sub-cat 'a1'..'a3' [cells F3..H3] and get the result [cells F4..H4].

I am trying to use a recursive formula. Can someone kindly help me out?

	A	B	C	D	E	F	G	H
1	category	sub-cat	result
2	b	b3	r7		category	a
3	c	c3	r2		sub-cat	a1	a2	a3
4	a	a1	r8			r8	r4	[empty]
5	b	b1	r1
6	a	a2	r4		category	b
7	b	b2	r5		sub-cat	b1	b2	b3
8	c	c1	r3			r1	r5	r7
9	c	c2	r6

<colgroup><col span="3" style="text-align: center;"><col style="text-align: center;"><col span="4" style="text-align: center;"></colgroup><tbody>
</tbody>

Many Thanks!

 

[RoryA]

Something like:

=IFERROR(LOOKUP(2,1/($A$1:$A$1000=$F$2)/($B$1:$B$1000=F$3),$C$1:$C$1000),"")

 

[kevincarbonaro]

Hi,

Thanks for that, I really appreciate it!

It seems to be working fine. I am finding it very interesting as once I did something similar but had gone through a more complex formula (where I had lost my hardisk and excel file) but I was using Match/Offset/Indirect (incl. making the formula recursive). [Banged my head a couple of times to the wall]

This is much simpler to understand!

regards,
Kevin

 

[RoryA]

Glad to help - welcome to the forum too!