Formula too long

[a2clan]

Hello,

I am trying to have a word appear in a cell when a number in inserted in the cell to the left of it. I am using a list of another sheet to store the number and words. Eg (A1) 1 = (B1) Brentwood, (A2) 1 = (B2) Billericay. So when I type 1 into A1, Brentwood would automatically appear in B1.

I have used the following formula for a small number, but my list is too big and means the formula is too big (Too many arguments). Here it is:

=IF(f2=Sheet1!A$1, Sheet1!B$1,IF(f2= Sheet1!A$2, Sheet1!B$2,IF(f2= Sheet1!A$3, Sheet1!B$3,IF(f2= Sheet1!A$4, Sheet1!B$4,IF(f2= Sheet1!A$5, Sheet1!B$5) ,IF(f2= Sheet1!A$6, Sheet1!B$6,IF(f2= Sheet1!A$7, Sheet1!B$7,IF(f2= Sheet1!A$8, Sheet1!B$8,IF(f2= Sheet1!A$9, Sheet1!B$9,IF(f2= Sheet1!A$10, Sheet1!B$10,IF(f2= Sheet1!A$11, Sheet1!B$11,IF(f2= Sheet1!A$12, Sheet1!B$12,IF(f2= Sheet1!A$13, Sheet1!B$13,IF(f2= Sheet1!A$14, Sheet1!B$14,IF(f2= Sheet1!A$15, Sheet1!B$15,IF(f2= Sheet1!A$16, Sheet1!B$16,IF(f2= Sheet1!A$17, Sheet1!B$17,IF(f2= Sheet1!A$18, Sheet1!B$18, IF(f2= Sheet1!A$19, Sheet1!B$19,IF(f2= Sheet1!A$20, Sheet1!B$20,IF(f2= Sheet1!A$21, Sheet1!B$21,IF(f2= Sheet1!A$22, Sheet1!B$22,IF(f2= Sheet1!A$23, Sheet1!B$23,IF(f2= Sheet1!A$24, Sheet1!B$24,IF(f2= Sheet1!A$25, Sheet1!B$25,IF(f2= Sheet1!A$26, Sheet1!B$26,IF(f2= Sheet1!A$27, Sheet1!B$27,IF(f2= Sheet1!A$28, Sheet1!B$28,IF(f2= Sheet1!A$29, Sheet1!B$29,IF(f2= Sheet1!A$30, Sheet1!B$30,IF(f2= Sheet1!A$31, Sheet1!B$31)))))))))))))))))))))))))))))))

I have added the $ symbol so I can drag the formula without the inputs changing.

Many thanks,

Luke

[brokenbiscuits]

=vlookup(A1,Sheet1!$A:$B, 2, false)

[Pete_UK]

You need to have unique numbers in column A - is that a typo when you say A2 = 1 and B2 = Billericay ?

If so, then you can use VLOOKUP, like this:

=VLOOKUP(F2,Sheet1!A:B,2,0)

That's all you need to replace that monster formula.

Hope this helps,

Pete

[Norie]

Luke

Try this

Please Login or Register  to view this content.

[a2clan]

Thank you, it seems to have worked

[Pete_UK]

You're welcome - 3 people suggested the same solution, so I think it's a fair bet that it would work.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete