Need help with custom formulas

[jbrad]

Hi
I'm working on a very large spreadsheet and think I need a custom formula that will automatically fill in cells on a row based on a unique number code. So for example, say the code for a particular place is 350, is there a way so that whenever 350 is entered in a1, "London" will display in b1, "John Smith" in c1, and so on? At the moment I've having to enter all the information manually in each cell and I'm sure there must be a way of automating this. I'm OK with dates and numbers, just can't work out how to get text to display based on numbers. Any help GREATLY appreciated, thank you.

[TMS]

If you have a reference table of codes, locations and names, you can use VLOOKUP to return matching data.

For example: =VLOOKUP(A1, 'Sheet2'!$A$2:$E$500,2,FALSE)


Regards, TMS

[AndyLitch]

Look into Vlookup and offset ...
Alternative upload a sample file and I can set up an example of how to use them

[jbrad]

Thanks - where do I upload a file?

[AndyLitch]

Bottom right of the message area is a "Go advanced" button.... After that you get a paperclip in the menu above the message area. Select that and follow the wizard.

[scott.s.fower]

i think what you are looking for is =vlookup().

so you would have a list of all of your places (ie 350) and in the following columns on the same row you would have the data that you would want to "automatically" fill in.

lets say this is the list on sheet vlookup
cola-colb---colc... <--the dashes are only so the columns line up with the data below
350 London John Smith...
250 Paris---Jean Forgeron


lets say
on a separate page your "place" is in a1, it would be =iferror(vlookup(A1,vlookup!$A$1:$F$100,2,0),"") where A1,vlookup!$A$1:$F$100 is the data set that you have on the vlookup page, 2 is the column relative to the data set, and 0 is an exact match

one limitation if you have multiple items associated with 350 then it will only find the first instance

[TMS]

@Andy: how are you thinking of using OFFSET? It's a volatile function so it's best to avoid it if possible.

Regards, TMS

[jbrad]

example.xls
Please see attached. I want to make it so that whenever a number is entered in column a, certain names etc appear in column b and so on.#
Thank you!

[AndyLitch]

I use offset rather than Vlookup quite often when i'm selecting a value from a list whic relates directly to the lookup table. I know it asn't always appropriate but i prefer it to lookup when it IS possible

[AndyLitch]

Example1.xls

There you go ... I put the Vlookup on the same sheet so you can figure out how it works

[oeldere]

See the attached file with Vlookup.

change the blue cell and see what's happing in the green cells.

[jbrad]

oeldere - Are you able to save in 2003 format? I don't have 2007 and when I open your file it says some of the formulas won't work in 2003. Thanks.

[oeldere]

Here you go (excel 2003 file)

[jbrad]

Thanks - but all that's appearing in B1 is =_xlfn.IFERROR(G1VERT.ZOEKEN($A1,Sheet1!$A$2:$C$4,2,0),"") and in C1 is =_xlfn.IFERROR(VLOOKUP($A1,Sheet1!$A$2:$C$4,3,0),"")
Getting #NAME? in both cells no matter what's entered in A1

[oeldere]

And know the 2003 version without failure.

[jbrad]

Thank you! This is what I need! Now, is there a general form of that function I can use so that whenever any number between 1 and 1000 is entered in a cell in column A, the relevant corresponding managers and names appear in columns B and C?

[oeldere]

=Vlookup($A1,Sheet1!$A$2:$C$4,2,0)

look up cell A1

in sheet 1 in the range A2:c24 (change the range for your need)

take the 2th value in that row (in this example the value in the B-column).

find an exact match (0) => 1 is the other option.

[jbrad]

Thanks - this is what I'm looking for!!!
Now, is there a way to make a single formula so that for any number between 1 and 999 entered in column A, a corresponding word will appear in columns B and C?

I mean:

whenever 102 is entered in a cell in column A, "Manchester" appears in the next cell in column B, "John" appears in the next cell in column C
whenever 103 is entered in column A, "London" appears in column B and "Paul" in column C

and so on....
?

[oeldere]

What about giving a try?

The answer is yes, but the data need to be found in the table on sheet 1.

In this example, 103 is not in the list and you will get #N/A as answer.

[jbrad]

Sorry, ignore my last post - I hadn't refreshed and your answer came up.
How do I change the range?

[oeldere]

Change

Please Login or Register  to view this content.

in e.g.

Please Login or Register  to view this content.

[jbrad]

Thanks - almost there now. In the formula =VLOOKUP($A2,Sheet1!$A$2:$C$4,2,0) - I have changed this to (A2,Sheet1!$A$2:$C$1500,2,0) - do I need to change the 2,0 at the end to anything different?

[oeldere]

Look at your formula in column C and notice the differance.

P.s. you changed the formula in A2 instead of $A2.

That can be inportant if you want to copy the formula (e.g. to the right)

[jbrad]

Thank you so much for your help. This is going to save me a huge amount of time!

[oeldere]

Thanks for the feedback.

Glad i could help.

If you question is solved, will you mark it as solved.

If you have other questions on this item, just ask.

You can add points to the rep(utation) of members who helped you, by clicking on the star on the left side.

[TMS]

I'm kinda confused. Nothing new there, but the final solution looks pretty much like the answer I gave you in post #2 just five minutes after you asked the question ...

And it's taken 2 hours and 24 replies to get to this point. What am I missing?

Regards, TMS

[arlu1201]

jbrad,

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.