Combining a list and a lookup.

[mbailey]

Hello,

I am trying to do the following:

In A1 I have a pulldown list: 10%, 20% ... upto 100% (Buffer)
In B1 I have a pulldown list: 50% or 100% (band)

I then have a seperate table which states the insurance cost for each seperate buffer and band formatted as this:

A5 B5 C5
Band Buffer Insurance
50% 100% 10%

I want in cell D1 to perform a lookup for me. If I chose 20% in A1, and 50% in B1 I want it to lookup in seperate table and tell me the value.

I'm stuck! Thanks for any help you can offer

Martin

 

[moonfish]

If you can rearrange your data to make it look like this, you can use index/match
to find any of the percentages:

Excel Workbook
	A	B	C	D	E	F	G	H
1	*	50%	100%	band	*	*	*	*
2	10%	75%	29%	*	*	band	buffer	insurance
3	20%	86%	11%	*	*	50%	100%	10%
4	30%	85%	55%	*	*	*	*	*
5	40%	82%	13%	*	*	*	*	*
6	50%	8%	10%	*	*	*	*	*
7	60%	78%	65%	*	*	*	*	*
8	70%	26%	76%	*	*	*	*	*
9	80%	72%	84%	*	*	*	*	*
10	90%	45%	4%	*	*	*	*	*
11	100%	10%	66%	*	*	*	*	*
12	buffer	*	*	*	*	*	*	*
Sheet1

If this isn't possible you can create a unique identifier for each entry in your list:

Excel Workbook
	A	B	C	D	E	F	G	H
1	buffer	band	key	insurance	*	*	*	*
2	10%	50%	0.10.5	a	*	band	buffer	insurance
3	10%	100%	0.11	b	*	50%	100%	s
4	20%	50%	0.20.5	c	*	*	*	*
5	20%	100%	0.21	d	*	*	*	*
6	30.0%	50%	0.30.5	e	*	*	*	*
7	30.0%	100%	0.31	f	*	*	*	*
8	40.0%	50%	0.40.5	g	*	*	*	*
9	40.0%	100%	0.41	h	*	*	*	*
10	50.0%	50%	0.50.5	i	*	*	*	*
11	50.0%	100%	0.51	j	*	*	*	*
12	60.0%	50%	0.60.5	k	*	*	*	*
13	60.0%	100%	0.61	l	*	*	*	*
14	70.0%	50%	0.70.5	m	*	*	*	*
15	70.0%	100%	0.71	n	*	*	*	*
16	80.0%	50%	0.80.5	o	*	*	*	*
17	80.0%	100%	0.81	p	*	*	*	*
18	90.0%	50%	0.90.5	q	*	*	*	*
19	90.0%	100%	0.91	r	*	*	*	*
20	100.0%	50%	10.5	s	*	*	*	*
21	100.0%	100%	11	t	*	*	*	*
Sheet2