Jump to content

excel vlookup equivalent?


This topic is 5703 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

Hi

I want to move a database from Excel to FileMaker. I have most of the conversion sorted out, but one thing that I want to do is convert a field of up to 5 characters to morse code. I have this working well in Excel using the following formula:

=IF(MID(S2,1,1)="","",IF(MID(S2,2,1)="",VLOOKUP(MID(S2,1,1),Code!A:B,2),IF(MID(S2,3,1)="",VLOOKUP(MID(S2,1,1),Code!A:B,2)&" "&VLOOKUP(MID(S2,2,1),Code!A:B,2),IF(MID(S2,4,1)="",VLOOKUP(MID(S2,1,1),Code!A:B,2)&" "&VLOOKUP(MID(S2,2,1),Code!A:B,2)&" "&VLOOKUP(MID(S2,3,1),Code!A:B,2),IF(MID(S2,5,1)="",VLOOKUP(MID(S2,1,1),Code!A:B,2)&" "&VLOOKUP(MID(S2,2,1),Code!A:B,2)&" "&VLOOKUP(MID(S2,3,1),Code!A:B,2)&" "&VLOOKUP(MID(S2,4,1),Code!A:B,2),VLOOKUP(MID(S2,1,1),Code!A:B,2)&" "&VLOOKUP(MID(S2,2,1),Code!A:B,2)&" "&VLOOKUP(MID(S2,3,1),Code!A:B,2)&" "&VLOOKUP(MID(S2,4,1),Code!A:B,2)&" "&VLOOKUP(MID(S2,5,1),Code!A:B,2))))))

It's probably not the most compact code (!) but it works. It looks at each character in a cell (S2 in this case) and then does a lookup to another sheet (Code) for the equivalent value. It also makes sure that blanks are not added if there are less than 5 characters.

How do I go about doing this in FileMaker? I am not sure where to even start!

Many thanks in advance

J

Link to comment
Share on other sites

BTW you could achieve this without a custom function if you want with just a substitute function in a calculation.

Substitute ( Upper(EnteredText); ["A"; ".-"]; ["B"; "-..."]; ["C"; "-.-."]; ... ["Z"; "--.."]; [" "; ""]

etc

Edited by Guest
Removed Left Function
Link to comment
Share on other sites

Well, that was a big ooops... :

Technically, since the field is supposed to have 5 characters. The Left function is not even needed as long as the validation is done on the source field.

Link to comment
Share on other sites

This topic is 5703 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.