Quite a Hard Excel Problem For you!

[The Boosh!]

Here is the formula:: =IF(C5="","",VLOOKUP(C5,T92:U323,2,0)) (apologies for lack paragraphs, IE5 at work doesn't know how to do them) What i want is to edit the formula so it if i type in something in the vlookup table (EG if i type in IND in the box it displays as INDIA in another cell) which it already does but i want to edit it so that people can just type in "India" instead of IND.... That is really not that clear but if you think you know you can help PM me

 

[Brand86]

Explain this a bit more? Not a pro but might be able to help. Not sure if V Lookups do pattern matching like that though think they need to be an exact match.

 

[iain187]

I have a feeling you might be using the wrong function for what you are trying to achieve; are you editing the data in T92:U323? vlookup just looks through T92:U323 for what you type in C5 and brings back the cell to the right of it, in your case. I'm sure you know that but I am just saying the T92:U323 isn't meant to be changing I think.

Can you explain an example of what you want in each cell and what the correct outcome should be?

 

[The Boosh!]

Right bare with me here... and apologies for lack of paragraphs (Again)... the VLOOKUP is a list of country codes - ie AFG is in one cell and next to it it says afganistan. If a user enters AFG into the cell im telling the formula to look at, then the cell that contains the vlookup formula will be replaced with afganistan. And vice versa for every country in the world. I want to try and add the function for the users of my sheet to just type in "afganistan" and it display "afganistan" the nationality box (IE the cell the formula is in) without the user having to know the country code (AFG). So as it stands, to enter a nationality the user has to know the country code. What im trying to do is make it so they can manually type the country without having to know it (its faster if they know it but not everyone knows all the worlds country codes LOL). I realise i could add a seperate box and put in it =e7 or whatever but was just wandering if there was a way to edit the current formula... hope this is a bit clearer?

 

[richy22]

Errr unless im being retarted why not just let them write "Afghanistan" then???!!!

I dont see what relevance this has thats all (maybe its your explanation!)

 

[The Boosh!]

I see where your coming from richy but its about time saving... IE writing BFS in a box is a lot quicker then writing Peoples Democratic Rebublic of BurkinoJust for efficiency really and id like the option to do both - ie vlookup and write... Looks like il need a seperate box for ppl who want to write it in.

 

[AK]

It would take you a bit of extra time but could you not just add the full names into the Vlookup table?

I.e have ENG = England
AND England = England LOL

Would take a bit of time but that's the only way i can think of doing it ?

 

[richy22]

The only way I see it is for a separate box/cell due im afraid!

I know what your saying about time saving ...BUT... how much time will a person save typing Democratic Republic of Congo (that took me 10-15secs and im a slow typer)... compared to having it done in look ups and it going wrong then having to call someone over and having to spend 5 minutes sorting it out??

Do you see where im coming from?? Just i've had to deal with s**te like this for aaaaaaaaaaagggees!

 

[biojo]

y not just use a drop down box with them all listed?

also in =IF(C5="","",VLOOKUP(C5,T92:U323,2,0)) why use 0 and not FALSE?

 

[The Boosh!]

Zippo - brilliant idea il just have to do that...Richy, yeh i do know what you mean mate but im not a manager or anything so just design the spreadsheet to what they want (no matter what makes sense/doesn't make sence)... Gay TBH... biojo, if i just put false it wouldn't be an exact match, ie would look up the next best thing. 0 ensures data validation. Thanks everyone for your help

 

[iain187]

are the codes always 3 letters? I'm thinking of something like:

if c5 string length > 3 then vlookup on the full name, if not then vlookup on the 3 letter code?

 

[The Boosh!]

are the codes always 3 letters? I'm thinking of something like:

if c5 string length > 3 then vlookup on the full name, if not then vlookup on the 3 letter code?

Yeh mate they are always 3 letters...

Had a talk with the "Manager" and told him its not possible to do it how he wants to do it (weather he likes it or not) so il be okay now...

But thankyou i appreciate your input!

 

[iain187]

here is an example:

 

[mradam87]

biojo, if i just put false it wouldn't be an exact match, ie would look up the next best thing. 0 ensures data validation. Thanks everyone for your help

Isn't it the other way round - FALSE ensures an exact match is found...?

 

[The Boosh!]

here is an example:

Thats one hell of a formula... let me get my head round it and il post back... thankyou ...

Mr adam.... Makes no difference anyway as it works ATM

 

[iain187]

lol, never say anything is not possible

in english it means:

if c5 is empty, output "please enter..."

else, if C5 = 3 characters long (i.e. it's a code not a full name) then do a vlookup on the code

else, if it's not 3, (it must be a full name) so do a vlookup on the next column (notice how the cell range is moved over one)


you will have to adjust the vlookup tables to suit yourself too obviously

 

[The Boosh!]

lol, never say anything is not possible

in english it means:

if c5 is empty, output "please enter..."

else, if C5 = 3 characters long (i.e. it's a code not a full name) then do a vlookup on the code

else, if it's not 3, (it must be a full name) so do a vlookup on the next column (notice how the cell range is moved over one)


you will have to adjust the vlookup tables to suit yourself too obviously

Thats a goodun

Your an excel god

 

[mradam87]

Very clever formula that, could come in handy for some of my work spreadsheets. I always wondered if there was a way of looking up a value based on a string length, just proves there is!

 

[MarkCup]

Here is the formula:: =IF(C5="","",VLOOKUP(C5,T92:U323,2,0))...

Modify this to include an 'OR' function refering to another cell containing the full word?

I don't know the correct syntax, but you can work that out.