Lookup question

[st471438]

Hi Everyone</SPAN></SPAN>

I tried to find similar problem and solutions by searching the older posts in forums but I didn’t find anything so I apologize if this is a repeated question but I would really appreciate any answer and help.</SPAN></SPAN>

1) I have a data set updated every week – left hand side it will have City names (Let’s say in Column A) and all right hand columns will include Week 1, Week 2 etc. I am planning to create a simple dashboard type of report that contains combo box of City names and when user selects the city, the numbers from the data set for the last available week displays. I know this can be achieved by using Index/Offset/Match – just not sure how to use the formula as nested formula.</SPAN></SPAN>

My goal is whenever the latest week data is available, I can just simply paste that data in data set which will then display that number as last data set number corresponding to the city has been selected through combo box. </SPAN></SPAN>

2) While creating this – I wish to include two combo box – one to select City name and another to select Week – is it possible? If so can anyone please provide me both formulas ? for my Question 1 and Question 2</SPAN></SPAN>

Thank you so very much in advance.</SPAN></SPAN>​

 

[circledchicken]

Hi,

Assuming your list of city names is constant, perhaps try something like this:

Excel Workbook
	A	B	C	D	E
1	City	Week 1	Week 2	Week 3	Week 4
2	A	1	4	7	10
3	B	2	5	8	11
4	C	3	6	9	12
5
6	Select City	C
7	Select Week	Week 4
8	Answer	12
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B8	=INDEX(2:4,MATCH(B6,A2:A4,0),MATCH(B7,1:1,0))

 

[st471438]

Hi,

Assuming your list of city names is constant, perhaps try something like this:

Sheet1

	A	B	C	D	E
1	City	Week 1	Week 2	Week 3	Week 4
2	A	1	4	7	10
3	B	2	5	8	11
4	C	3	6	9	12
5
6	Select City	C
7	Select Week	Week 4
8	Answer	12

<THEAD>
</THEAD><TBODY>
</TBODY>

Excel 2010

Worksheet Formulas Cell Formula B8 =INDEX(2:4,MATCH(B6,A2:A4,0),MATCH(B7,1:1,0)) <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

Hi - sorry I should had to mention that I am using 2003 version at work.

 

[circledchicken]

That should work in earlier versions as well (including 2003).

 

[st471438]

Not working

 

[Skybluekid]

Try =INDEX($B$2:$E$4,MATCH(B6,A2:A4,0),MATCH(B7,1:1,0))

 

[st471438]

Still keep giving N#A

 

[Skybluekid]

Try =INDEX($B$2:$E$4,MATCH(B6,$A$2:$A$4,0),MATCH(B7,$B$1:$E$1,0)). Sorry should have checked

 

[Skybluekid]

Try again =INDEX($B$2:$E$4,MATCH(B7,$A$2:$A$4,0),MATCH(B6,$B$1:$E$1,0)). Sorry should have checked