Lookup and Find

[Pestomania]

I feel like I should know this but am drawing a blank. I have a table of 3 columns and 10 rows and two values to locate.

In column 1, I want to find the number 29850 and I want to locate in column 2 the term "report" and return the value in column 10.

So an idea is as below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sequence[/TD]
[TD]Type[/TD]
[TD]Order[/TD]
[/TR]
[TR]
[TD]29960[/TD]
[TD]Report[/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]10099[/TD]
[TD]Test[/TD]
[TD]Incomplete[/TD]
[/TR]
[TR]
[TD]19045[/TD]
[TD]Report[/TD]
[TD]Half-Complete[/TD]
[/TR]
[TR]
[TD]29850[/TD]
[TD]Report[/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]29850[/TD]
[TD]Test[/TD]
[TD]Incomplete[/TD]
[/TR]
</tbody>[/TABLE]


So using this, It would locate in the first column "29850" and find the term "report" in column 2, and return the phrase "complete"

 

[MARK858]

One-way...

Book1
	A	B	C	D
1	Sequence	Type	Order
2	29960	Report	Complete	Complete
3	10099	Test	Incomplete
4	19045	Report	Half-Complete
5	29850	Report	Complete
6	29850	Test	Incomplete
Sheet3

Cell Formulas
Range	Formula
D2	{=INDEX($C$2:$C$6, MATCH(29850&"Report",$A$2:$A$6&$B$2:$B$6, 0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Pestomania]

One-way...

	A	B	C	D
Sequence	Type	Order
Report	Complete	Complete
Test	Incomplete
Report	Half-Complete
Report	Complete
Test	Incomplete

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]29960[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]10099[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]19045[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]29850[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]29850[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=INDEX($C$2:$C$6, MATCH(29850&"Report",$A$2:$A$6&$B$2:$B$6, 0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

That worked perfect! Thank you so much!

 

[DanteAmor]

Here are a couple of non-array formula

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:139.72px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:78.89px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Sequence</td><td >Type</td><td >Order</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">29960</td><td >Report</td><td >Complete</td><td style="text-align:right; ">29850</td><td >Report</td><td >Complete</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">10099</td><td >Test</td><td >Incomplete</td><td > </td><td > </td><td >Complete</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">19045</td><td >Report</td><td >Half-Complete</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">29850</td><td >Report</td><td >Complete</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">29850</td><td >Test</td><td >Incomplete</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >F2</td><td >=IFERROR(INDEX(C2:C6,SUMPRODUCT((A2:A6=D2)*(B2:B6=E2)*ROW(C2:C6))-ROW(A1)),"Not found")</td></tr><tr><td >F3</td><td >=IFERROR(LOOKUP(2,1/(D2&E2=A2:A6&B2:B6),C2:C6),"Not found")</td></tr></table></td></tr></table>

 

[MARK858]

Just another non-array formula

Book1
	A	B	C	D	E
1	Sequence	Type	Order
2	29960	Report	Complete	29850	Complete
3	10099	Test	Incomplete	Report
4	19045	Report	Half-Complete
5	29850	Report	Complete
6	29850	Test	Incomplete
Sheet3

Cell Formulas
Range	Formula
E2	=INDEX($C$2:$C$6,MATCH(1,INDEX((D2=$A$2:$A$6)*(D3=$B$2:$B$6),0,1),0))