Tyring to shorten a formula ..!

G

Guest

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.
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top