Tyring to shorten a formula ..!

[Monk]

Hi.

Im probably doing this completely wrong but i want the spreadsheet to return
a value when you enter a number.. ?

Ok.. simple terms.
I have say 100 product codes and their product description detailed on the
spreadsheet. If I enter a product code in to cell E4 (for example) I wish the
cell with the formula in it to return the product description

I have managed this using the below formula..

=IF(E4=1,L8,"")&IF(E4=2,L7,"")&IF... and so on to product No. 100

But im finding im having to enter a very long formula and there is only a
specific length the formula can be before it wont let me add any more.

Is there away of shortening the formula or entering a completly different
one to get the same result .??
Many thanks.. J.

[N Harkawat]

Look at Help for the function VLOOKUP

"Monk" <[email protected]> wrote in message
news:[email protected]...
> Hi.
>
> Im probably doing this completely wrong but i want the spreadsheet to
> return
> a value when you enter a number.. ?
>
> Ok.. simple terms.
> I have say 100 product codes and their product description detailed on the
> spreadsheet. If I enter a product code in to cell E4 (for example) I wish
> the
> cell with the formula in it to return the product description
>
> I have managed this using the below formula..
>
> =IF(E4=1,L8,"")&IF(E4=2,L7,"")&IF... and so on to product No. 100
>
> But im finding im having to enter a very long formula and there is only a
> specific length the formula can be before it wont let me add any more.
>
> Is there away of shortening the formula or entering a completly different
> one to get the same result .??
> Many thanks.. J.
>

[B. R.Ramachandran]

Hi,
This could be a possibility, if (a) the product codes (and descriptions) are
in contained in sequential rows (i.e., with no blank rows in between), (b)
the info for each product is contained in only one row.
If the product codes are in column K and descriptions in column L (say
starting from row 2 to row 101), and you want the description for a product
to appear in F4 based on the code you enter in E4,
the formula for F4 would be
=OFFSET($L$2,MATCH($E$4,$K$2:$K$101,0)-1,0)
Hope this works!
Regards,
B.R.Ramachandran




"Monk" wrote:

> Hi.
>
> Im probably doing this completely wrong but i want the spreadsheet to return
> a value when you enter a number.. ?
>
> Ok.. simple terms.
> I have say 100 product codes and their product description detailed on the
> spreadsheet. If I enter a product code in to cell E4 (for example) I wish the
> cell with the formula in it to return the product description
>
> I have managed this using the below formula..
>
> =IF(E4=1,L8,"")&IF(E4=2,L7,"")&IF... and so on to product No. 100
>
> But im finding im having to enter a very long formula and there is only a
> specific length the formula can be before it wont let me add any more.
>
> Is there away of shortening the formula or entering a completly different
> one to get the same result .??
> Many thanks.. J.
>