Having issues with LOOKUP

[joetogo]

Hi team,

I'm having random issues with lookup, hopefully I'm doing it right, but for some reason I'm getting an "N" value for a lookup that
should be returning "Network". It works for most of the other cells bar a few.

Basically i have a ref table on Sheet2 with Keywords (services) and Definitions (categorizations) and Sheet1 under categorizations
goes the lookup formula:

=LOOKUP(E1+100,SEARCH(Sheet2!$A$2:$A$69,Sheet2!A2),Sheet2!$B$2:$B$69)

I get the first desired result, as does the next, but then there's a random N in there as well. I cannot figure out why, can somebody
please assist?

I've attached an example for potential ref.

Thank you team

 

[Aladin Akyurek]

The formula should be:

=LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!$A$2:$A$69,$C3),Sheet2!$B$2:$B$69)

But it looks like

=VLOOKUP(C2&"*",Sheet2!A:B,2,0)

would suffice.

The problem these formulas might get into is that you have duplicates in Sheet2, that is, some keywords are listed twice while associated with a different category. For example:

[TABLE="class: grid, width: 356"]
<tbody>[TR]
[TD]WAN Service Foundation@SILVER[/TD]
[TD]Network[/TD]
[/TR]
[TR]
[TD]WAN Service Foundation@SILVER[/TD]
[TD]N[/TD]
[/TR]
</tbody>[/TABLE]

 

[joetogo]

The formula should be:

=LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!$A$2:$A$69,$C3),Sheet2!$B$2:$B$69)

But it looks like

=VLOOKUP(C2&"*",Sheet2!A:B,2,0)

would suffice.

The problem these formulas might get into is that you have duplicates in Sheet2, that is, some keywords are listed twice while associated with a different category. For example:

[TABLE="class: grid, width: 356"]
<tbody>[TR]
[TD]WAN Service Foundation@SILVER[/TD]
[TD]Network[/TD]
[/TR]
[TR]
[TD]WAN Service Foundation@SILVER[/TD]
[TD]N[/TD]
[/TR]
</tbody>[/TABLE]

Ah i see! I get it now, so i converted the references into table, removed the dupes, and then end result is fixed. Thank you very much for your help!

 

[Aladin Akyurek]

Ah i see! I get it now, so i converted the references into table, removed the dupes, and then end result is fixed. Thank you very much for your help!

You are welcome.