Lookup formual help Please.

[srizki]

How can I change the formula,

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" align=left width=64 height=24 x:str="'=LOOKUP(9.99999999999999E+307,SEARCH(E1996,'[Lookup-Master-table.xls]CC'!$A$2:$A$45),'[Lookup-Master-table.xls]CC'!$B$3:$B$45)">=LOOKUP(9.99999999999999E+307,SEARCH(E1996,'[Lookup-Master-table.xls]CC'!$A$2:$A$45),'[Lookup-Master-table.xls]CC'!$B$3:$B$45)</TD></TR></TBODY></TABLE>

So, if master list does not have the lookup value, it should bring the lookup values instead of #N/A error.

 

[Jonmo1]

Which version of Excel do you have?

 

[srizki]

which version of excel do you have?

2003

 

[Jonmo1]

2003

Try

=IF(ISERROR(LOOKUP(9.99999999999999E+307,SEARCH(E1996,'[Lookup-Master-table.xls]CC'!$A$2:$A$45))),E1996,LOOKUP(9.99999999999999E+307,SEARCH(E1996,'[Lookup-Master-table.xls]CC'!$A$2:$A$45),'[Lookup-Master-table.xls]CC'!$B$3:$B$45))

I wasn't quite clear on what you wanted returned if the formula was an error
So I just made it return E1996 (highlighted Red), change as you see fit.

 

[srizki]

Thank you Jonmo1.

I was wondering if there is another way of doing this without repeating the whole formula. or using any other formula.

Thanks.

 

[Jonmo1]

There is...
what type of data is expected to be returned by the formula when it doesn't error?
Text or Number?

And what exactly do you want returned when it is an error?
And what is in E1996

 

[srizki]

There is...
what type of data is expected to be returned by the formula when it doesn't error?
Text or Number?

number

And what exactly do you want returned when it is an error?
And what is in E1996

E1996 is cell reference.

and I if master list does not have the lookup value, it should return the cell value instead of #N/A or any other error. as you just showed me.

Thanks.

 

[Jonmo1]

E1996 is cell reference. <-duh, I knew that. What VALUE is in E1996

and I if master list does not have the lookup value, it should return the cell value (what cell, E1996?) instead of #N/A or any other error. as you just showed me.

Thanks.

And what type of data is normally returned by your originally posted formula when it is not an error.
Does it return TEXT or NUMBER?

 

[srizki]

And what type of data is normally returned by your originally posted formula when it is not an error.
Does it return TEXT or NUMBER?

I use this formula for both, text and for numbers and it works well.

<TABLE style="WIDTH: 112pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=149 border=0 x:str><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=79 height=20 x:num>1109376</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=70>Ops Techs</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num>1109376</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Ops Techs</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num>1109376</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Ops Techs</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num>1109376</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Ops Techs</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num>1109376</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Ops Techs</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num>1109376</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Ops Techs</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num>1109376</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Ops Techs</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num>1109376</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Ops Techs</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num>1109376</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Ops Techs</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num>1109376</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Ops Techs</TD></TR></TBODY></TABLE>
here is an example.

Thanks