Data Table

[pwill]

Hi can anyone help with a formula or macro?

I have Data in columns A,B,C and have created a table E1:G10 where I want to find the last ranges used in Column B for each name in Column A using Column C as the Reference.

ie

	A	B	C	D	E	F	G	H	I	J
1	Name	Range	Ref		Name	Ref	Last range used
2	1	1 - 5	5		1	5	11 - 15
3	1	6 - 10	5		1	10	21 - 30
4	1	11 - 15	5		1	15	31 - 45
5	1	1 - 10	10		2	5	6 - 10
6	1	11 - 20	10		2	10	11 - 20
7	1	21 - 30	10		2	15	16 - 30
8	1	1 - 15	15		3	5	11 - 15
9	1	16 - 30	15		3	10	21 - 30
10	1	31 - 45	15		3	15	31 - 45
11	2	1 - 5	5
12	2	6 - 10	5
13	2	1 - 10	10
14	2	11 - 20	10
15	2	1 - 15	15
16	2	16 - 30	15
17	3	1 - 5	5
18	3	6 - 10	5
19	3	11 - 15	5
20	3	1 - 10	10
21	3	11 - 20	10
22	3	21 - 30	10
23	3	1 - 15	15
24	3	16 - 30	15
25	3	31 - 45	15
26
27
28

<colgroup><col style="width:48pt" width="64" span="5"> <col style="mso-width-source:userset;mso-width-alt:1280;width:26pt" width="35"> <col style="mso-width-source:userset;mso-width-alt:1974;width:41pt" width="54"> <col style="mso-width-source:userset;mso-width-alt:3401;width:70pt" width="93"> <col style="width:48pt" width="64" span="3"> </colgroup><tbody>
</tbody>

I am struggling to create a formula to put in column G to find the last range used using the name and ref in columns A - C

any help with this would be appreciated

regards
pwill

 

[Eric W]

Try this in G2 and drag down:

=LOOKUP(2,1/(($A$2:$A$30=E2)*($C$2:$C$30=F2)),$B$2:$B$30)

If your table is not in order like you have it shown, we can adjust it. Hope this helps.

 

[pwill]

Try this in G2 and drag down:

=LOOKUP(2,1/(($A$2:$A$30=E2)*($C$2:$C$30=F2)),$B$2:$B$30)

If your table is not in order like you have it shown, we can adjust it. Hope this helps.

Hi Eric, I adjusted to meet my needs and works perfect,

Thank you, much appreciated.

pwill

 

[Eric W]

Happy to help!