[Solved] Help with converting an excel formula

[chilli76]

Hi guys

I'd appreciate some help with a formula I know works in excel but for the life of me I can't get it to work with calc. Changed the separators but still doesn't work. Not sure if there's something in the formula calc doesn't recognise?

From:
=INDEX(Prices!$A$2:$A400,MATCH("*"&VLOOKUP(AB7,AI21:AO40,2,0)&"*",Prices!$A$2:$A400,0)+2)

To:
INDEX(Prices.$A$2:$A400;MATCH("*"&VLOOKUP(AB7;AI21:AO40;2;0)&"*";Prices.$A$2:$A400;0)+2)


Anyone see where I'm going wrong?

[Villeroy]

Save the file in Open Document Format (*.ods)
Tools>Options>Calc>Calculation>Match whole cell = OFF

Remove "*"& and &"*"

[chilli76]

Hi

I don't seem to have that option in the calculate menu?

[Villeroy]

It is something like "= and <> match whole cell". Dont have an English version at hand right now.

[RoryOF]

It is "Search criteria = and <> must apply to whole cells" in the English versions.

[chilli76]

OK done that but nothing changes.

Is the new formula then:

INDEX(Prices.$A$1:$A700;MATCH(VLOOKUP(AB7;AI20:AO40;2;0);Prices.$A$1:$A700;0)+2)

after removing the "*"&

[Villeroy]

Turn OFF "regular expressions" on the same options page.

[chilli76]

Still no joy i'm afraid. Definitely works in excel but it seems to be finding cell A2 in 'Prices' because if I change the +2 at the end it returns me that many cells below A2. Should be finding a match for AB7 down A1:A700 in 'Prices' and returning me the cell 2 places below that. Don't know why it's not?

[RoryOF]

Are your entries all of the correct variable type? ?View /Value highlighting should tell you.

[chilli76]

Apologies in advance but I am quickly getting out of my depth here. I'm afraid I don't know what that means. I've clicked value highlighting and all the inputs turned green. Presuming this is good?

I have to admit to not following the original suggestion to the letter and I've now found that what I was supposed to do does actually work........ie save the excel file as ods and change the options. BUT the excel file was just a test file and I need to apply that same formula in my original ods file (with slightly different reference cells). Converting the excel file is no good for me.

Sorry to be a pain!

[RusselB]

You'd have to edit the formula to use the correct references.
There's no way for Calc to know that you need different references when copying a formula from one spreadsheet to another.
What you seem to be at, is a point where some manual work is required.

[chilli76]

That's the thing though, I have changed all the reference cells and I just can't see why it's not working the same. There must be something as it quite clearly works when saving the excel file as .ods. Can't see why it doesn't in my original file. Hey ho, I'll get there in the end

[RoryOF]

Best then to make a very simple test file, with a very few cells of data, and get that working. It should then be easy for you to scale up exactly to your target file.

[Villeroy]

Are you really, really sure that this formula does what it is supposed to do in MS Excel?
I mean, this formula is horrible.

[chilli76]

Hi

Please see attached example of formula in use. I am trying to get the prices from the 'Prices' sheet into my 'ENTRY 2' sheet as shown by the colour coding. The problem arises when I use the formula in another workbook. Even changing the reference cells it doesn't seem to work. Please, If you know of another way to find the correct prices in the prices sheet referencing either Column C or CF, I'd appreciate it.

Thanks

[Villeroy]

F3: =INDEX(Prices.$A$2:$A400;MATCH(VLOOKUP(C3;$CQ$1:$CR$21;2;0);Prices.$A$2:$A400;0)+2) ==> 1.9

dissected into 3 steps
F15: =VLOOKUP(C3;$CQ$1:$CR$21;2;0) ==> "Aston Villa" [correct, C3 "Aston Villa" is found in CQ3 and the value in CR3 is "Aston Villa" too]
F16: =MATCH(F15;Prices.$A$2:$A400;0)+2 ==> 172 [correct, "Aston Villa is matched at position 170 within Prices.A2:A400 and +2 yields 172]
F17: =INDEX(Prices.$A$2:$A400;F16) ==> 1.9 [correct, the 172nd value in Prices.$A$2:$A400 is 1.9]

The formula words as designed although I'm unsure if this is what it is intended to do.

[chilli76]

Hi

Yes that is exactly what it was intended to do. I have copied some elements of the sheet I want it to work in on to the attached file and I just can't figure out why it doesn't work the same in the highlighted green cell. It must be something I'm doing wrong but for the life of me I can't work out what.

[Villeroy]

=VLOOKUP(AB3;$AI$20:$AS$40;2;0) is the failing part because there is nothing like AB3 in AI20:AI40

It looks for any "Newcastle United" within AI20:AI40 but there is only "Newcastle". It would find "Newcastle" within "Newcastle United"

[chilli76]

Sorry, that should read AM20:AS40

[chilli76]

Ah ha..................it is a problem with the merged cells. If I put the list in unmerged adjacent rows ie AJ20@AK40 it works fine.

Excellent, thanks for all your help guys.

[chilli76]

Hi once again

Just one thing I still have to do manually that is quite irksome if anyone knows of a way to get around this I'd appreciate it.

At the moment I use a simple formula

='Web Data 15.D231'

to bring in league data to my ENTRY 2 league table.

The one issue is that until about 5 or 6 weeks into a season the row on which I can find this data changes ie 231 becomes 261. Currently I just change this manually but wondered about a formula to automate.

Can I use a VLOOKUP or INDEX/MATCH to find the correct row I need to start taking my data from? Problems I see are that the beginning of the table I need it is the 3rd appearance of the team name ie there are 2 more instances above it and I need a VLOOKUP or INDEX?MATCH to know that it's looking for the 3rd instance before reading off any data.

Anyone know a way?

Many thanks

[Villeroy]

All this has been tried many thousands of times and failed.
You never get any solution which would work for anybody else but you (at best).
Spreadsheets evolved as arithmetic calculators during the 80ies but the vast majority of today's users try so desparately misusing it as a database (and fail).
I would start newly from scratch with a completely different data layout using a database which is a much heavier tool for lists that are related to other lists. This would be development work. Nothing for newbees. Not for people who learn (nothing) by doing.