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;

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Column 3[/TD]
[TD]Column 4[/TD]
[/TR]
[TR]
[TD]Location number[/TD]
[TD]Location Name[/TD]
[TD]Number name[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Bristol[/TD]
[TD]Main[/TD]
[TD]012010304785[/TD]
[/TR]
[TR]
[TD]1235[/TD]
[TD]Bristol[/TD]
[TD]Secondary[/TD]
[TD]012035422524[/TD]
[/TR]
[TR]
[TD]1236[/TD]
[TD]Bristol[/TD]
[TD]Emergency[/TD]
[TD]012035487204[/TD]
[/TR]
[TR]
[TD]1245[/TD]
[TD]Manchester[/TD]
[TD]Main
[/TD]
[TD]016145685243[/TD]
[/TR]
[TR]
[TD]1246[/TD]
[TD]Manchester[/TD]
[TD]Secondary[/TD]
[TD]016146484125[/TD]
[/TR]
[TR]
[TD]1247[/TD]
[TD]Manchester[/TD]
[TD]Emergency[/TD]
[TD]016124554541[/TD]
[/TR]
[TR]
[TD]1256[/TD]
[TD]Leeds[/TD]
[TD]Main[/TD]
[TD]034554848424[/TD]
[/TR]
[TR]
[TD]1257[/TD]
[TD]Leeds[/TD]
[TD]Secondary[/TD]
[TD]034562545485[/TD]
[/TR]
[TR]
[TD]1258[/TD]
[TD]Leeds[/TD]
[TD]Emergency[/TD]
[TD]034565848155[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Location Code[/TD]
[TD]1247[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Secondary[/TD]
[TD]016146484125
[/TD]
[/TR]
</tbody>[/TABLE]

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[TABLE="width: 500"]
<tbody>[TR]
[TD]016146484125[/TD]
[/TR]
</tbody>[/TABLE]
</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.