Multiple lookups

[goofy78270]

Is it possible to do a lookup with multiple values?

I have the following sample data in 6 different worksheets that I would like to find the rate within the first renewal column. I want to create two summary tables 1) that contains the worst rates (highest values) and 2) that contains the best rates (lowest values) between all 6 worksheets

Excel Workbook
	D	E	F	G	H	I	J	K	L	M	N
1	PRODUCT_TYPE	LOW_TERM	HIGH_TERM	LOW_LTV	HIGH_LTV	LOW_SCORE	HIGH_SCORE	COVERAGE	BASE_UPFRONT_RATE	BASE_FIRST_RENEWAL	BASE_SECOND_RENEWAL
2	Arm	0	40	0	85	680	699	6	0	0.47	0.2
3	Arm	0	40	0	85	680	699	12	0	0.53	0.2
4	Arm	0	40	0	85	700	999	6	0	0.42	0.2
5	Arm	0	40	0	85	700	999	12	0	0.45	0.2
6	Arm	0	40	85.01	90	680	699	12	0	0.64	0.2
7	Arm	0	40	85.01	90	680	699	25	0	1.13	0.2
8	Arm	0	40	85.01	90	700	999	12	0	0.57	0.2
9	Arm	0	40	85.01	90	700	999	25	0	0.82	0.2
10	Arm	0	40	90.01	95	680	699	16	0	1.13	0.2
11	Arm	0	40	90.01	95	680	699	25	0	1.48	0.2
12	Arm	0	40	90.01	95	680	699	30	0	1.68	0.2
13	Arm	0	40	90.01	95	700	999	16	0	0.85	0.2
14	Arm	0	40	90.01	95	700	999	25	0	1.08	0.2
15	Arm	0	40	90.01	95	700	999	30	0	1.22	0.2
16	Buydown	0	40	0	85	680	699	6	0	0.47	0.2
17	Buydown	0	40	0	85	680	699	12	0	0.53	0.2
18	Buydown	0	40	0	85	700	999	6	0	0.42	0.2
19	Buydown	0	40	0	85	700	999	12	0	0.45	0.2
20	Buydown	0	40	85.01	90	680	699	12	0	0.64	0.2
21	Buydown	0	40	85.01	90	680	699	25	0	1.13	0.2
22	Buydown	0	40	85.01	90	700	999	12	0	0.57	0.2
23	Buydown	0	40	85.01	90	700	999	25	0	0.82	0.2
24	Buydown	0	40	90.01	95	680	699	16	0	1.13	0.2
25	Buydown	0	40	90.01	95	680	699	25	0	1.48	0.2
26	Buydown	0	40	90.01	95	680	699	30	0	1.68	0.2
27	Buydown	0	40	90.01	95	700	999	16	0	0.85	0.2
28	Buydown	0	40	90.01	95	700	999	25	0	1.08	0.2
29	Buydown	0	40	90.01	95	700	999	30	0	1.22	0.2
30	Fixed	0	40	0	85	680	699	6	0	0.34	0.2
PMI-M-Standard 09-18-08

Excel 2003


If this method is not the best way or if you have other ideas on how to accomplish such a task, I would greatly appreciate the feedback.

 

[Dryver14]

the only way I could think to do the above is on each page, rank the column you are interested in

Then on summary page Lookup 1 to 6 with their return values down a column of 36 rows ie 1 to 6 on page 2 1 to 6 on page 3 etc then rank that column.

the same for 25 to 30 in a seperate column then rank that too

A little bit of setting up I know but once in place the lookups will do the work