Hi there,
I am hoping someone could help me come up a formula that would allow me to find a cell in a range of both columns and rows.
I am trying to find an address say "123 New York, NY" and then return who lives at that address. The trouble is "123 New York, NY" could be anywhere in the range B2:CC8 and once the formula finds it, I want to return who lives at that address which is available in column A.
I'm not sure how this will look, but here's an image of what I'm trying to do:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]...[/TD]
[TD]CC[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Address1[/TD]
[TD]Address2[/TD]
[TD]Address3[/TD]
[TD]Address4[/TD]
[TD]Address5[/TD]
[TD]Address6[/TD]
[TD]...[/TD]
[TD]Address77[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Peter[/TD]
[TD]111 Dallas, TX[/TD]
[TD] 321 Los Angeles, CA[/TD]
[TD] 123 Chicago, IL[/TD]
[TD]555 Houston, TX[/TD]
[TD] 111 San Francisco, CA[/TD]
[TD] 22 San Diego, CA[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sam[/TD]
[TD]99 Omaha, NE[/TD]
[TD] 123 New York, NY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Greg[/TD]
[TD] 123 Miami, FL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sarah[/TD]
[TD]345 Anchorage, AK[/TD]
[TD]555 Boston, MA[/TD]
[TD] 999 Baltimore, MD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]George[/TD]
[TD]666 Washington, D.C.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Michael[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Anna[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I would like a formula that could search B2:CC8 and find "123 New York, NY" and then return Sam.
Any help?
Thanks!
I am hoping someone could help me come up a formula that would allow me to find a cell in a range of both columns and rows.
I am trying to find an address say "123 New York, NY" and then return who lives at that address. The trouble is "123 New York, NY" could be anywhere in the range B2:CC8 and once the formula finds it, I want to return who lives at that address which is available in column A.
I'm not sure how this will look, but here's an image of what I'm trying to do:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]...[/TD]
[TD]CC[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Address1[/TD]
[TD]Address2[/TD]
[TD]Address3[/TD]
[TD]Address4[/TD]
[TD]Address5[/TD]
[TD]Address6[/TD]
[TD]...[/TD]
[TD]Address77[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Peter[/TD]
[TD]111 Dallas, TX[/TD]
[TD] 321 Los Angeles, CA[/TD]
[TD] 123 Chicago, IL[/TD]
[TD]555 Houston, TX[/TD]
[TD] 111 San Francisco, CA[/TD]
[TD] 22 San Diego, CA[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sam[/TD]
[TD]99 Omaha, NE[/TD]
[TD] 123 New York, NY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Greg[/TD]
[TD] 123 Miami, FL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sarah[/TD]
[TD]345 Anchorage, AK[/TD]
[TD]555 Boston, MA[/TD]
[TD] 999 Baltimore, MD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]George[/TD]
[TD]666 Washington, D.C.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Michael[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Anna[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I would like a formula that could search B2:CC8 and find "123 New York, NY" and then return Sam.
Any help?
Thanks!