Search columns A OR B for result in C

[Sleepingsouls]

Hi Guys,

Having a little trouble with a look up I have tried Vlookup with no luck and I have Tried Index Match... But I think I'm missing something important - or at least not getting it exactly right.

[TABLE="width: 512"]
<colgroup><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]ID[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]John [/TD]
[TD]Smith[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Search[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]James[/TD]
[TD]Samuels[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]ID[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Jacky[/TD]
[TD]Grant[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 448"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD]What I want to be able to do is search for either column A or B and return C as the result - So if I type in John it will return 1 or if I type in Smith it will return 1.

Thank you.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

How do you propose handle duplicates, especially if you are wanting to search both columns?
For example, what if you had someone named "James Samuels" and another person named "Jesse James"?

The probability of duplicate names within one column is already high, but will be even higher if now searching across two columns.

 

[Sleepingsouls]

This was just an example in my actual database there are no duplicates. They are location names and then the code name given to those locations.

 

[Joe4]

Why not try nesting two VLOOKUPs (one for each column) inside an IFERROR statement?
So if the first VLOOKUP fails because it cannot find it, it will do the second?

The structure of the function would look like this:

=IFERROR(VLOOKUP(...),VLOOKUP(...))

If it is possible that it might not be found at all in either, than you can nest inside another IFERROR, i.e.

=IFERROR(IFERROR(VLOOKUP(...),VLOOKUP(...)),"Not found")

 

[Sleepingsouls]

Ooooo I'll give that a try! Thanks so much Joe I'll let you know how it goes. As is always the case I try to get to creative and look over the simple things.

 

[Sleepingsouls]

Thanks Joe that worked!

 

[Joe4]

You are welcome.