Finding Data in a Table Using Multiple search Criteria

[Redlad91]

Hi,

I am looking to create a document which can find a specific bit of information based on multiple criteria.

Currently i have a function which looks at a table for a Specific phone number, What i want to do is pull more a number based on a location code and the name of the number for example see below;

Column 1	Column 2	Column 3	Column 4
Location number	Location Name	Number name	Number
1234	Bristol	Main	012010304785
1235	Bristol	Secondary	012035422524
1236	Bristol	Emergency	012035487204
1245	Manchester	Main	016145685243
1246	Manchester	Secondary	016146484125
1247	Manchester	Emergency	016124554541
1256	Leeds	Main	034554848424
1257	Leeds	Secondary	034562545485
1258	Leeds	Emergency	034565848155

<tbody>
</tbody>

For the formula to work i want to be able to put in a location number in a box, and then the detail is given depending on the number i want i.e.

	1	2
A	Location Code	1247
B	Secondary	016146484125

<tbody>
</tbody>

The location code is a free form box so any number can be entered in here, the Number name is already on the sheet, I just want them to both be used and enter the number in the b2 box.

Currently for a single criteria search i am using - =IF(B2="","",(INDEX(Sheet3!F:F,MATCH("*"&B2&"*",Sheet3!A:A,))))

Any help would be appreciated but let me know if i haven't explained it that well
****** id="cke_pastebin" style="position: absolute; top: 410.4px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">Cui

016146484125

<tbody>
</tbody>

</body>

 

[AlanY]

try

Book1
	A	B	C	D
1	Location number	Location Name	Number name	Number
2	1234	Bristol	Main	12010304785
3	1235	Bristol	Secondary	12035422524
4	1236	Bristol	Emergency	12035487204
5	1245	Manchester	Main	16145685243
6	1246	Manchester	Secondary	16146484125
7	1247	Manchester	Emergency	16124554541
8	1256	Leeds	Main	34554848424
9	1257	Leeds	Secondary	34562545485
10	1258	Leeds	Emergency	34565848155
11
12
13	Location Code	1247
14	Secondary	16146484125
Sheet3

Cell Formulas
Range	Formula
B14	{=INDEX(D2:D10,MATCH(INDEX(B2:B10,MATCH(B13,A2:A10,0))&A14,B2:B10&C2:C10,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.