Lookup Issue help please

[stephen.smith]

Hi Guys
I am trying to make a formula which in the example below will fit into column D and will reference a cell in B to see what the result from the combo box is and then based on this will lookup the table in columns e:h and then will find the corresponding value from the input column C that matches in the lookup table. The problem is that the input figure will have a different note depending on whether it comes from e:f or g:h so the formula has to be able to understand that if the combo box returns a value of 2 it only looks in g:h and if it returns 1 it only looks in E:F. Can anybody help. I have spent days pulling my hair out over this.Thanks
Stephen

(A)..............(B).............(C)........(D)..........(E)...(F)...(G)...(H)...
Combo Box...Combo Result...Input...Table Result...1....Note...2...Note...
.......................2...............110.........ZXY........95...ABC...95...DEF...
.......................1...............95..........ABC........110..PQR...110..ZXY

 

[Dazzawm]

Download, install and use this to post your spreadsheet. It will be more understandable and you will get more replies.

http://www.excel-jeanie-html.de/html/down_en.php

 

[stephen.smith]

Here is the box pasted below. Maybe this will help you out. Thanks for anyone that can help.

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

 

[stephen.smith]

Hey I have figured out the problem, the cell with the manual input is not actually manually input its a cell which finds the max value of a range of cells from a different worksheet. How can i fix the formula to make this work?

 

[markmzz]

If I understand correct what you want, this can help you:

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Combo Box</TD><TD>Combo Result</TD><TD>Manual Input</TD><TD>Table Result</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Lookup Table</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1,1</TD><TD>NOF</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1,2</TD><TD>DEF</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">1,1</TD><TD style="TEXT-ALIGN: center">ABC</TD><TD style="TEXT-ALIGN: center">NOF</TD><TD style="TEXT-ALIGN: center">SAS</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1,4</TD><TD>LKO</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">1,2</TD><TD style="TEXT-ALIGN: center">DEF</TD><TD style="TEXT-ALIGN: center">MEF</TD><TD style="TEXT-ALIGN: center">MJB</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1,2</TD><TD>MEF</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">1,3</TD><TD style="TEXT-ALIGN: center">HIJ</TD><TD style="TEXT-ALIGN: center">POF</TD><TD style="TEXT-ALIGN: center">MKK</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1,1</TD><TD>ABC</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">1,4</TD><TD style="TEXT-ALIGN: center">KLM</TD><TD style="TEXT-ALIGN: center">JUF</TD><TD style="TEXT-ALIGN: center">LKO</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1,4</TD><TD>JUF</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">1,5</TD><TD style="TEXT-ALIGN: center">MNO</TD><TD style="TEXT-ALIGN: center">LKD</TD><TD style="TEXT-ALIGN: center">POL</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">1,6</TD><TD style="TEXT-ALIGN: center">PQR</TD><TD style="TEXT-ALIGN: center">LCD</TD><TD style="TEXT-ALIGN: center">LLL</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD></TR></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D2</TH><TD style="TEXT-ALIGN: left">=INDEX($H$3:$J$8,MATCH($C2,$G$3:$G$8,0),MATCH($B2,$H$2:$J$2,0))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz

 

[stephen.smith]

This doesnt work it gives me back a result #NA

 

[markmzz]

This doesnt work it gives me back a result #NA

You solved the problem in the topic below?

http://www.mrexcel.com/forum/showthread.php?p=2783502#post2783502

If not


In C2:C7 you have formulas?


What is the precision of the numbers in C2:C7?


Markmzz