Dynamic lookup formula

srizki

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,860
Office Version
  1. 365
Platform
  1. Windows
I am using this following formula,

=LOOKUP(9.99999999999999E+307,SEARCH(N2,Activity!$D$2:$D$163),Activity!$E$2:$E$163)

I frequently add some items in the master list, and therefore, I have to update the formula on every sheet. How can I make this a dynamic formula.

Thanks
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
srizki said:
I am using this following formula,

=LOOKUP(9.99999999999999E+307,SEARCH(N2,Activity!$D$2:$D$163),Activity!$E$2:$E$163)

I frequently add some items in the master list, and therefore, I have to update the formula on every sheet. How can I make this a dynamic formula.

Thanks
Click to expand...

Do you mean that Activity!$D$2:$D$163 and Activity!$E$2:$E$163 frequently changes? And what kind of data (text, number) do you have in these ranges?
 
Upvote 0
Aladin Akyurek said:
Do you mean that Activity!$D$2:$D$163 and Activity!$E$2:$E$163 frequently changes? And what kind of data (text, number) do you have in these ranges?
Click to expand...

Yes, that is the master list, and very often I find some new items to be added to master list, so it changes and therefore I have to change formula on nine sheets.

I have changed the formula many times. I have used some extra blank rows, and they were not enough.

Thanks.
 
Upvote 0
srizki said:
Yes, that is the master list, and very often I find some new items to be added to master list, so it changes and therefore I have to change formula on nine sheets.

I have changed the formula many times. I have used some extra blank rows, and they were not enough.

Thanks.
Click to expand...

Do these ranges...

Activity!$D$2:$D$163

Activity!$E$2:$E$163

change? If they do, what kind of data do they house: text or number?

How is the LOOKUP formula (referring to the above ranges) related to 9 sheets?

Is this master list bit relevant to the problem of changing ranges? If so, how?


 
Upvote 0
srizki said:
I am using this following formula,

=LOOKUP(9.99999999999999E+307,SEARCH(N2,Activity!$D$2:$D$163),Activity!$E$2:$E$163)

I frequently add some items in the master list, and therefore, I have to update the formula on every sheet. How can I make this a dynamic formula.

Thanks
Click to expand...
Assuming that the data involved is TEXT.

Define the following named ranges:
  • Range1 (or whatever descriptive name you want to use)
  • Refers to: =Activity!$D$2:INDEX(Activity!$D$2:$D$1000,MATCH("zzzzz",Activity!$D$2:$D$1000))
  • Range2 (or whatever descriptive name you want to use)
  • Refers to: =Activity!$E$2:INDEX(Activity!$E$2:$E$1000,MATCH("zzzzz",Activity!$E$2:$E$1000))
In each case adjust for a reasonable end of range. I used down to row 1000. Use a last row that gives you plenty of room for exapnsion of your list.

Then your lookup formula becomes:

=LOOKUP(1E100,SEARCH(N2,Range1),Range2)
 
Upvote 0
T. Thank you, but I have not tried your furmula yet.

Aladin,

here is an example of master list data.
Activity column D and Descripion Column E. Activity is number.


<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>
<TABLE style="WIDTH: 149pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=198 border=0 x:str><COLGROUP><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4608" width=126><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl30 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: green" width=72 height=34>Activity</TD><TD class=xl27 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: navy" width=126>Activity Description</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>117</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>121</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>122</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>124</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>126</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>136</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>138</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>152</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>153</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>155</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>157</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>160</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>164</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR></TBODY></TABLE>​

All nine sheets have Activity (numbers) in column N.

'=LOOKUP(9.99999999999999E+307,SEARCH(N2,Activity!$D$2:$D$195),Activity!$E$2:$E$195)

and you see right now, the last row number in master list is 195, (Including 3 blank rows, in case I have to add more),

So, when there is a number in column N of any of the monthly 9 sheets, that is not in the master list it gives me an error on all 17000 rows of the sheet where this new numbers if found, The reason for #N/A error on all cells of column is that I am using =SUMIF(..) formula using the column where lookup formula gives the error.

=SUMIF($AF$2:$AF$16762,AF2,$AC$2:$AC$16762)
and
=SUMPRODUCT(($AF$2:$AF$14258=AF2)*($AD$2:$AD$14258=AD2),$AC$2:$AC$14258)


I hope I explained it better than before.

Thanks
 
Last edited:
Upvote 0
srizki said:
T. Thank you, but I have not tried your furmula yet.

Aladin,

here is an example of master list data.
Activity column D and Descripion Column E. Activity is number.


<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>
<TABLE style="WIDTH: 149pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=198 border=0 x:str><COLGROUP><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4608" width=126><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl30 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: green" width=72 height=34>Activity</TD><TD class=xl27 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: navy" width=126>Activity Description</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>117</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>121</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>122</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>124</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>126</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>136</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>138</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>152</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>153</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>155</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>157</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>160</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>164</TD><TD class=xl29 dir=ltr style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126>Productive Warranty</TD></TR></TBODY></TABLE>​

All nine sheets have Activity (numbers) in column N.

'=LOOKUP(9.99999999999999E+307,SEARCH(N2,Activity!$D$2:$D$195),Activity!$E$2:$E$195)

and you see right now, the last row number in master list is 195, (Including 3 blank rows, in case I have to add more),

So, when there is a number in column N of any of the monthly 9 sheets, that is not in the master list it gives me an error on all 17000 rows of the sheet where this new numbers if found, The reason for #N/A error on all cells of column is that I am using =SUMIF(..) formula using the column where lookup formula gives the error.

=SUMIF($AF$2:$AF$16762,AF2,$AC$2:$AC$16762)
and
=SUMPRODUCT(($AF$2:$AF$14258=AF2)*($AD$2:$AD$14258=AD2),$AC$2:$AC$14258)


I hope I explained it better than before.

Thanks
Click to expand...
Still hard to visualize...

Just checking: Wouldn't...

=INDEX(Activity!$E$2:$E$195,MATCH(N2,Activity!$D$2:$D$195,0))

do the same as the LOOKUP formula you have?
 
Upvote 0
Aladin Akyurek said:
Still hard to visualize...

Just checking: Wouldn't...

=INDEX(Activity!$E$2:$E$195,MATCH(N2,Activity!$D$2:$D$195,0))

do the same as the LOOKUP formula you have?
Click to expand...

Yes it does do the same thing, but again, if I add something in the master list, I have to change the formula in all sheet, so, if I use say row 1000 in lookup or Index formula, would it work fine, even though the master list will have only 200 or so rows?

Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Lookup 2 criteria and return yes or no
Replies
3
Views
132
RoryA
RoryA
L
  • Question Question
Sum of a dynamic number of rows
2
Replies
10
Views
551
Peter_SSs
Peter_SSs
R
  • Question Question
Linked dynamic named ranges - unexpected results.
Replies
2
Views
199
Ramses505
R
O
  • Solved
Dynamic (row) selection in index formula based on date selection
2
Replies
10
Views
529
DanteAmor
DanteAmor
srizki
  • Question Question
Highlight the variances on two sheets.
Replies
2
Views
212
srizki
srizki

Forum statistics

Threads
1,224,585
Messages
6,179,700
Members
452,938
Latest member
babeneker

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top