Dynamic lookup formula

[srizki]

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

 

[Aladin Akyurek]

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

Right.

Define Lrow by means of Insert | Name | Define (or Formulas | Name Manager) as referring to:

=MATCH(9.99999999999999E+307,Activity!$D:$D)

Define ActList as referring to:

=Activity!$D$2:INDEX(Activity!$D:$D,Lrow)

and DescList as referring to:

=Activity!$E$2:INDEX(Activity!$E:$E,Lrow)

Now you can invoke on any sheet...

=INDEX(DescList,MATCH(N2,ActList,0))

If you don't want #N/A and you are on 2007 or later, try:

=IFERROR(INDEX(DescList,MATCH(N2,ActList,0)),"Not Found")

On an earlier Excel version, try:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"Not Found",INDEX(DescList,MATCH(N2,ActList,0))))

 

[srizki]

Thank you very much,

My another problem is that I need to creat a pivot table using all the tables, i.e. 9 sheets. each sheet containg averating 15000 rows and 36 columns.

Thanks you again.
I will see how can I do this, I am using 2003 version.

 

[T. Valko]

T. Thank you, but I have not tried your furmula yet.

Understood!

 

[srizki]

Thanks T.