Multiple Text Search

[shmiggs_2010]

I have a workbook that I use as a template to import into a billing program. The only issue with is that the software we use, has its an odd naming protocol for the tax areas. I was able to export all the data into a workbook.

I need a formula that is able to search for the State, then the City, and output the Tax Area. There were a couple of promising solutions but they involved numbers, and unfortunately our software doesn't associate the cities to any numbers.

This is an array formula that I found on exceltactics.com, but it uses a number to output a result.
{=INDEX(A4:C1159,MATCH(1,(A4:A1158=A2)*(B4:B1158=B2),0),4)}


This is a sample of how I have things set up, there are about 2500 rows of cities we bill and would really like a solution. [TABLE="class: outer_border, width: 800"]
<tbody>[TR]
[TD]State (input)[/TD]
[TD]City (input)[/TD]
[TD]Tax Area (result)[/TD]
[/TR]
[TR]
[TD]AL[/TD]
[TD]Auburn[/TD]
[TD]=INDEX(A4:C10,MATCH(1,(A4:A10=A2)*(B4:B10=B2),0),3)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]State (static info)[/TD]
[TD]City (static info)[/TD]
[TD]Tax Rate/Area (static info)[/TD]
[/TR]
[TR]
[TD]AL[/TD]
[TD]AUBURN[/TD]
[TD]AUBURN AL[/TD]
[/TR]
[TR]
[TD]AL[/TD]
[TD]BALDWIN[/TD]
[TD]BALDWIN AL[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]LOS BANO[/TD]
[TD]LOS BANOCA[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]SOUTH SA[/TD]
[TD]SOUTH SACA[/TD]
[/TR]
[TR]
[TD]GA[/TD]
[TD]BALDWIN[/TD]
[TD]BALDWIN GA[/TD]
[/TR]
[TR]
[TD]IA[/TD]
[TD]DES MOIN[/TD]
[TD]DES MOINIA[/TD]
[/TR]
</tbody>[/TABLE]
I hope I explained myself well enough, thank you!

 

[Matt Rogers]

Hi.

Your formula is ok, but it is an "Array Formula".
Remember to press CTRL+SHIFT+ENTER combination whenever you enter or edit an array formula.

Take a look:
Array Formulas in Excel – Excel Array Formula Syntax & Array Constants | Excel & VBA – Databison

<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='4' style='font-family:Arial; color:#000000; background-color:#FFFFFF; font-size:10px; font-weight:bold; font-style:normal; '><colgroup><col width='28pt'><col width='119,25pt'><col width='119,25pt'><col width='119,25pt'></colgroup><tr style='background-color:#FAFAFA'><td align='middle' colspan='4'>Worksheet 'Sheet1'</td></tr><tr style='background-color:#cacaca'><td> </td><td align='middle'>A</td><td align='middle'>B</td><td align='middle'>C</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='left' style='color:#keine; ' >State (input)</td><td align='left' style='color:#keine; ' >City (input)</td><td align='left' style='color:#keine; ' >Name (result)</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='left' style='font-weight:normal; ' >Texas</td><td align='left' style='font-weight:normal; ' >Galveston</td><td align='left' style='font-weight:normal; ' >Matt Rogers</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='right' style='font-weight:normal; ' > </td><td align='right' style='font-weight:normal; ' > </td><td align='right' style='font-weight:normal; ' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='left' style='color:#keine; ' >State (static info)</td><td align='left' style='color:#keine; ' >City (static info)</td><td align='left' style='color:#keine; ' >Name (static info)</td></tr><tr><td style='background-color:#cacaca' align='middle'>5</td><td align='left' style='font-weight:normal; ' >New York</td><td align='left' style='font-weight:normal; ' >New York</td><td align='left' style='font-weight:normal; ' >Emily Parker</td></tr><tr><td style='background-color:#cacaca' align='middle'>6</td><td align='left' style='font-weight:normal; ' >New York</td><td align='left' style='font-weight:normal; ' >Albany</td><td align='left' style='font-weight:normal; ' >Madison Williams</td></tr><tr><td style='background-color:#cacaca' align='middle'>7</td><td align='left' style='font-weight:normal; ' >New York</td><td align='left' style='font-weight:normal; ' >Syracuse</td><td align='left' style='font-weight:normal; ' >David Blake</td></tr><tr><td style='background-color:#cacaca' align='middle'>8</td><td align='left' style='font-weight:normal; ' >Texas</td><td align='left' style='font-weight:normal; ' >Austin</td><td align='left' style='font-weight:normal; ' >Benjamin Forster</td></tr><tr><td style='background-color:#cacaca' align='middle'>9</td><td align='left' style='font-weight:normal; ' >Texas</td><td align='left' style='font-weight:normal; ' >Fort Worth</td><td align='left' style='font-weight:normal; ' >Mason McGill</td></tr><tr><td style='background-color:#cacaca' align='middle'>10</td><td align='left' style='font-weight:normal; ' >Texas</td><td align='left' style='font-weight:normal; ' >Galveston</td><td align='left' style='font-weight:normal; ' >Matt Rogers</td></tr><tr><td style='background-color:#cacaca' align='middle'>11</td><td align='left' style='font-weight:normal; ' >Texas</td><td align='left' style='font-weight:normal; ' >Glen Rose</td><td align='left' style='font-weight:normal; ' >Anthony Jones</td></tr><tr><td style='background-color:#cacaca' align='middle'>12</td><td align='left' style='font-weight:normal; ' >Texas</td><td align='left' style='font-weight:normal; ' >Pasadena</td><td align='left' style='font-weight:normal; ' >Olivia Taylor</td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Arial; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Cell</td><td>Formula</td></tr><tr><td>C2</td><td><Span style='color:#222222'>{=INDEX</Span><Span style='color:#0000DD'>(A5:C100,MATCH</Span><Span style='color:#222222'>(1,</Span><Span style='color:#0000DD'>(A5:A100=A2)</Span><Span style='color:#222222'>*</Span><Span style='color:#0000DD'>(B5:B100=B2)</Span><Span style='color:#222222'>,0)</Span><Span style='color:#0000DD'>,3)</Span><Span style='color:#222222'>}</Span></td></tr></table><table style='font-family:Arial; font-size:8pt; background-color:#FFFFFF'><tr><td style='font-weight:bold'>Array-Formula!</td></tr><tr><td><span>Ctrl+Shift + Enter </span><span style='text-decoration:underline'></span><span></span></td></tr><tr><td><span></span><span style='font-weight:bold; color:#FF0000'></span><span></span></td></tr></table>

 

[shmiggs_2010]

Wow thank you Matt!
For whatever reason it works on my Macbook but on my work computer it has issues. and yes I did press CTRL+SHIFT+ENTER. Either way, it works now!

 

[Matt Rogers]

Thanks for the feedback
Welcome at Mr Excel and all the best.

Matt