Please Fix Lookup Formula

[srizki]

<STYLE>st1\:* { BEHAVIOR: url(#ieooui)}</STYLE><STYLE>@font-face { font-family: Century Gothic;}@font-face { font-family: HGPSoeiKakugothicUB;}@font-face { font-family: @HGPSoeiKakugothicUB;}@page Section1 {size: 612.0pt 792.0pt; margin: 72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin: 36.0pt; mso-footer-margin: 36.0pt; mso-paper-source: 0; }P.MsoNormal { FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"; mso-fareast-font-family: HGPSoeiKakugothicUB; mso-style-parent: ""; mso-pagination: widow-orphan}LI.MsoNormal { FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"; mso-fareast-font-family: HGPSoeiKakugothicUB; mso-style-parent: ""; mso-pagination: widow-orphan}DIV.MsoNormal { FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"; mso-fareast-font-family: HGPSoeiKakugothicUB; mso-style-parent: ""; mso-pagination: widow-orphan}P { FONT-SIZE: 12pt; FONT-FAMILY: "Times New Roman"; mso-fareast-font-family: HGPSoeiKakugothicUB}SPAN.SpellE { mso-style-name: ""; mso-spl-e: yes}DIV.Section1 { page: Section1}</STYLE>

I need to use LOOKUP or INDEX&MATCH formual to bring matched values from column N to column AD, I am using this formula,

=IF(N2,"Presence","Presence",lookup(9.99999999999999e+307,SEARCH(N2,'[Lookup-Master-table.xls]Activity'!$D$2:$D$181).'[Lookup-Master-table.xls]Activity'!$E$2:$E$181)

What I want from this formula to do this, if there is word "Presence" in column N, put "Presence" in corresponding cell of column AD, other wise see Lookup_Master-table.
Lookup master does have Presence but that corresponds to any three digit number. Most of three digit numbers in column N have been converted to word Presence.

I need to adjust the above formula.

Thanks​

 

[Aladin Akyurek]

<STYLE>st1\:* { BEHAVIOR: url(#ieooui)}</STYLE><STYLE>@font-face { font-family: Century Gothic;}@font-face { font-family: HGPSoeiKakugothicUB;}@font-face { font-family: @HGPSoeiKakugothicUB;}@page Section1 {size: 612.0pt 792.0pt; margin: 72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin: 36.0pt; mso-footer-margin: 36.0pt; mso-paper-source: 0; }P.MsoNormal { FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"; mso-fareast-font-family: HGPSoeiKakugothicUB; mso-style-parent: ""; mso-pagination: widow-orphan}LI.MsoNormal { FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"; mso-fareast-font-family: HGPSoeiKakugothicUB; mso-style-parent: ""; mso-pagination: widow-orphan}DIV.MsoNormal { FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"; mso-fareast-font-family: HGPSoeiKakugothicUB; mso-style-parent: ""; mso-pagination: widow-orphan}P { FONT-SIZE: 12pt; FONT-FAMILY: "Times New Roman"; mso-fareast-font-family: HGPSoeiKakugothicUB}SPAN.SpellE { mso-style-name: ""; mso-spl-e: yes}DIV.Section1 { page: Section1}</STYLE>

I need to use LOOKUP or INDEX&MATCH formual to bring matched values from column N to column AD, I am using this formula,

=IF(N2,"Presence","Presence",lookup(9.99999999999999e+307,SEARCH(N2,'[Lookup-Master-table.xls]Activity'!$D$2:$D$181).'[Lookup-Master-table.xls]Activity'!$E$2:$E$181)

What I want from this formula to do this, if there is word "Presence" in column N, put "Presence" in corresponding cell of column AD, other wise see Lookup_Master-table.
Lookup master does have Presence but that corresponds to any three digit number. Most of three digit numbers in column N have been converted to word Presence.

I need to adjust the above formula.

Thanks​

Did you mean to run:

=IF(N2,"Presence","Presence",lookup(9.99999999999999e+307,SEARCH('[Lookup-Master-table.xls]Activity'!$D$2:$D$181,N2),'[Lookup-Master-table.xls]Activity'!$E$2:$E$181)

 

[Excelestial]

=IF(N2,"Presence","Presence",lookup(9.99999999999999e+307,SEARCH(N2,'[Lookup-Master-table.xls]Activity'!$D$2:$D$181,N2),'[Lookup-Master-table.xls]Activity'!$E$2:$E$181)

Looks like she had a period instead of a comma. Wouldn't she need a value to search, Alan? The N2?

 

[srizki]

Thanks to both of you, yes I was missing N as Aladin and Exeltial mentioned, but I am missing a parenthesis, there are 3 open parenthesis and two closing. I am putting the last closing parenthesis at the end, )), but I am getting message that "you've entered too many arguments for this function.
to get...

Thanks

 

[Aladin Akyurek]

Thanks to both of you, yes I was missing N as Aladin and Exeltial mentioned, but I am missing a parenthesis, there are 3 open parenthesis and two closing. I am putting the last closing parenthesis at the end, )), but I am getting message that "you've entered too many arguments for this function.
to get...

Thanks

Whoops. i didn't count the parens... We are also missing the = sgn, I guess. Does this...

=IF(N2="Presence","Presence",LOOKUP(9.99999999999999e+307,SEARCH('[Lookup-Master-table.xls]Activity'!$D$2:$D$181,N2),'[Lookup-Master-table.xls]Activity'!$E$2:$E$181))

work as needed?

 

[srizki]

Aladin,

The formula is working exactly they way I wanted.
So if the look-up value is text we put in "quotes" and if this is text we use just comma?

Thank you Aladin, and by the way this lookup 9.9999 search formula I learned from you among other formulas that I learned from you, and it is nice to see that Bill Jelen acknowledged your help to others in his book.

 

[Aladin Akyurek]

Aladin,

The formula is working exactly they way I wanted.
So if the look-up value is text we put in "quotes" and if this is text we use just comma?

Thank you Aladin, and by the way this lookup 9.9999 search formula I learned from you among other formulas that I learned from you, and it is nice to see that Bill Jelen acknowledged your help to others in his book.

You are welcome. Thanks for providing feedback.

Text like Presence...

1) can be put in a cell of its own, say, X2, and that cell can be used in formulas if appropriate, like in:

=LEFT(X2,3)

=VLOOKUP(X2,...)

=SUMIF(A2:F2,X2,A3:F3)

etc.

2) can directly used in formula like in:

=LEFT("Presence",3)

=VLOOKUP(Presence,...)

=SUMIF(A2:F2,"Presence",A3:F3)

etc.

Note. The foregoing assumes that Presence is not a name in Excel's name space, standing for something.

 

[srizki]

Thank you Aladin
Thank you very much.

 

[Aladin Akyurek]

Thank you Aladin
Thank you very much.

You are welcome.