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:

Sequence	Type	Order
29960	Report	Complete
10099	Test	Incomplete
19045	Report	Half-Complete
29850	Report	Complete
29850	Test	Incomplete

<tbody>
</tbody>

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
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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula D2 {=INDEX($C$2:$C$6, MATCH(29850&"Report",$A$2:$A$6&$B$2:$B$6, 0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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))