Index or Lookup Problem Help!!

[stephen.smith]

Hi Guys
I am having a problem with an index or lookup formula.
I have a combo box in cell A2 which returns a value in cell B2 (value may be 1,2,3,4,5). I then have a value which must be input in cell C2 and based on this value I need a formula in cell D2 to perform an index formula based on the result of the combo box (B2) in cells F2:O2, and then from the index result perform a lookup formula to find the appropriate value from the lookup table in cells F3:O99.
If anyone needs anymore info please let me know.
Thanks
Stephen

 

[snoopyhr]

I am not sure if I understood your setup properly, if your set up looks like this then the formula in D2 returns the value that intersect with row # in C2 and Col # B2

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Combo Box	Returned value from CB	Manual Input	Result from table
2		4	4	56		Item-1	Item-2	Item-3	Item-4
3						12	95	28	23
4						115	79	26	87
5						140	94	156	71
6						4	5	137	56
7						159	22	106	132
Sheet1

But there must be more to this ....

 

[stephen.smith]

HI Jeanie, its very close to what im looking for but in the lookup table you have a an extra column beside each value with a note about that value so I need the formula to find the corresponding note to the appropriate value. Does that make sense?
Thanks for the help so far.

 

[id107]

HI Jeanie, its very close to what im looking for but in the lookup table you have a an extra column beside each value with a note about that value so I need the formula to find the corresponding note to the appropriate value. Does that make sense?
Thanks for the help so far.

Where is the data that C2 is matched against stored? Is it in column E?

If it is, try: =INDEX(F3:O99,MATCH(C2,E3:E99,0),B2)

 

[stephen.smith]

Hi Craig
I have in column F Item 1, Column G, Item 1 Notes, Column H Item 2, Column I Item 2 Notes, Column J Item 3, Column K Item 3 Notes and so on.
So if the combo box returns Item 2 I need the formula to look at the value that is manually input into C and then reference H and then show the matched note for that value from I. Is this what you need. Thanks
Stephen

 

[stephen.smith]

Tried that craig but it doesnt work
How do i paste an example in here for you.

 

[id107]

I'm sure that there's a quite simple method of doing this with a formula, but I can't think of one at the moment with the data formatted as it is. This UDF will do the trick though

Function StephenSmithLookup() As String
 
    Application.Volatile
 
    Dim LookUpVal As String, LookUpNo As Integer, MyRow As Integer, MyCol As Integer
 
    LookUpVal = Application.ThisCell.Offset(, -1).Value
    LookUpNo = Application.ThisCell.Offset(, -2).Value
 
    MyCol = Application.WorksheetFunction.Match("Item " & LookUpNo, Range("F1:O1"), 0) + 5
    MyRow = Application.WorksheetFunction.Match(LookUpVal, Range(Cells(2, MyCol), Cells(99, MyCol)), 0)
 
    StephenSmithLookup = Cells(MyRow + 1, MyCol + 1).Value
 
End Function

paste that into a module and call it into cell D3 with =StephenSmithLookup()

 

[stephen.smith]

Hi Guys here is the problem pasted below. Maybe this will help to solve the problem.
Thanks

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Combo Box	Combo Result	Manual Input	Table Result			Lookup Table
2		2	1.1	NOF			1	1	2	2	3	3
3		1	1.2	DEF			1.1	ABC	1.1	NOF	1.1	SAS
4		3	1.4	LKO			1.2	DEF	1.2	MEF	1.2	MJB
5		2	1.2	MEF			1.3	HIJ	1.3	POF	1.3	MKK
6		1	1.1	ABC			1.4	KLM	1.4	JUF	1.4	LKO
7		2	1.4	JUF			1.5	MNO	1.5	LKD	1.5	POL
8							1.6	PQR	1.6	LCD	1.6	LLL
9
10	Its the formula for column D that I dont Know how to perform??? Can you help.
Sheet1

 

[snoopyhr]

Try this :

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Combo Box	Combo Result	Manual Input	Table Result			Lookup Table
2		2	1.1	NOF			1	1	2	2	3	3
3		1	1.2	DEF			1.1	ABC	1.1	NOF	1.1	SAS
4		3	1.4	LKO			1.2	DEF	1.2	MEF	1.2	MJB
5		2	1.2	MEF			1.3	HIJ	1.3	POF	1.3	MKK
6		1	1.1	ABC			1.4	KLM	1.4	JUF	1.4	LKO
7		2	1.4	JUF			1.5	MNO	1.5	LKD	1.5	POL
8							1.6	PQR	1.6	LCD	1.6	LLL
9
Sheet4

 

[stephen.smith]

This formula displays #NA for me, im using excel 2007. Does this make a difference