How would you take the lowest, middle, and highest number from three tables (example attached)

[abra]

I have three tables, and I am trying to pull the lowest, middle, and highest number for each item per set (as detailed below).

Each table has the same structure and the same Item names.

How would you recommend I do this?

Many thanks.

TABLE 1 - I want to populate this

tmp.xls
	A	B	C	D	E	F	G	H	I
1			PricebreaksPerUnit
2
3	Item		1-10	11-25	26-50	51-100	101-150	151-200	201-500
4	a	Low	159	223	24	47	etc	etc	etc
5	a	Medium	598	523	102	105
6	a	High	745	687	304	207
7	b	Low	etc
8	b	Medium
9	b	High
10	c	Low
11	c	Medium
12	c	High
13	d	Low
14	d	Medium
15	d	High
16	e	Low
17	e	Medium
18	e	High
19	f	Low
20	f	Medium
21	f	High
22	g	Low
23	g	Medium
24	g	High
How do I do this

Data populates from the tables below that are in the same worksheet

Table 1.

tmp.xls
	A	B	C	D	E	F	G	H	I	J	K
1		Item	Itemcode	ItemName	PricebreaksPerUnit
2
3					1-10	11-25	26-50	51-100	101-150	151-200	201-500
4		a	1	dog	159	523	102	249	744	474	882
5		b	2	cat	329	167	255	313	555	681	686
6		c	3	banana	543	195	685	206	997	113	242
7		d	4	phone	859	390	225	480	288	635	21
8		e	5	car	207	182	435	884	400	264	275
9		f	6	house	407	411	316	557	69	649	521
10		g	7	watch	941	237	83	43	95	321	931
Data_Table1

Table 2:

tmp.xls
	A	B	C	D	E	F	G	H	I	J	K
1		Item	Itemcode	ItemName	PricebreaksPerUnit
2
3					1-10	11-25	26-50	51-100	101-150	151-200	201-500
4		a	1	dog	598	687	24	105	938	428	560
5		b	2	cat	408	909	428	63	828	692	848
6		c	3	banana	112	595	21	934	722	940	274
7		d	4	phone	280	429	986	668	275	636	219
8		e	5	car	505	92	613	121	168	277	592
9		f	6	house	775	363	329	781	734	789	324
10		g	7	watch	655	198	492	641	138	963	756
Data_Table2

Table 3

tmp.xls
	A	B	C	D	E	F	G	H	I	J	K
1		Item	Itemcode	ItemName	PricebreaksPerUnit
2
3					1-10	11-25	26-50	51-100	101-150	151-200	201-500
4		a	1	dog	745	223	304	47	48	1	613
5		b	2	cat	622	915	139	909	385	485	706
6		c	3	banana	537	372	544	34	831	454	692
7		d	4	phone	534	143	277	890	333	93	474
8		e	5	car	951	140	359	957	730	989	299
9		f	6	house	260	227	362	933	827	19	705
10		g	7	watch	232	382	676	656	73	107	343
Data_Table3

 

[hkaplan2]

Use MIN, MEDIAN, MAX for low, medium, and high.

i.e.:

=min(100,200,300) returns 100
=median(100,200,300) returns 200
=max(100,200,300) returns 300

Just point to the data cells in your formula. Like:

=min(vlookup("a",mylowtable,4,false),vlookup("a",mymidtable,4,false),vlookup("a",mymaxtable,4,false)) or any variation that you can copy from cell to cell.