Formulas not working in Google sheets that work in Excel - Help?

[KatyMac]

I have a lovely spreadsheet in excel with lots of fab formula (Vlooup and Iferrors etc) & when I import it into Google sheets the formula don't work

Am I being daft or are they not compatable?

Your array - doesn't it need to have cell references rather than column references?

So $A$5:$AM$12

Ignore the question about Inputs, I thought it was a literal input function which does exist but I know nothing about!

A7 is the result of a formula - do I need to say it's the value not the formula

Where you have A7 you need to have what it is you want it to look up...

A look up is saying:

Look at this value/reference, then find it in this array, then count 8 cell positions to the left and give me that figure there

But does it lookup the value in the cell or the forumla?

In Excel it looks up the value

The value in the cell you reference not where you have placed the formula.

It doesn't have to be a value though it could be a date or a name

That formula needs something to find in the array so it could be

VLOOKUP("Smith",A7:AM7,8)....

But with dollar signs if you want to copy it

It will look up what is in A7...

Perhaps GS doesn't like the IFERROR part... what does the help say about error?

But I'm just using this bit now to see if it'll work =VLOOKUP(A7,Money!A$:AM$,8,FALSE)

& it doesn't

Try it with row references for the array as well
As the columns

Like this =VLOOKUP(A7,Money!$A$1:$AM$999,8,FALSE)

It will be something to do with the other tab you are referencing as Google says that format will work
=VLOOKUP(A3,$F$3:$G$8,2,false)

As a work around can you bring your reference table into the same sheet instead of having on a different sheet and redo the formula?

Yes, provided there is something in A7 to look up

I think it’s because Google Sheets doesn’t use the same way to reference a range of columns as Excel does. Try it by using A1:AW999 (or whatever) and see if that works. If it does then you know it’s an issue with how you reference a range of columns. Depending on how sophisticated your formulae need to be then that might be enough to fix your issue or you might need to try alternative options like INDIRECT or ARRAYFORMULA or even the data query function.

OK - I am obviously being very dim

I copied the sheets I am working on to the same sheet
All the data disappearred....when I type =A7 (or whatever) nothing is displayed

I am doing something basic and stupid wrong

Can you not build the formula from scratch in GS?

What do you mean when you say all the data disappeared? Can you no longer see it on the google sheet?

I've just had a play with IFERROR & VLOOKUP and to me it's not behaving any differently from Excel, it will even accept full column ranges A:B so I don't need A1:B9999

If your basic =A7 functions aren't showing the data, are they showing an error or just blank? Could you have a cell format issue?

I'm thinking cell format issue

It should both the formula nad the answer when you hover over it but it should show the value!! Surely!!

Working on the worng problem all this time!! it's formatting bugger!

We've all been there...

God I am so thick!

So how do I fix it?

You could try Format > Clear Formatting, and see if your =A7 result appears

Not thick, by the way, not if you're playing with IFERROR and VLOOKUP nesting! I'd be surprised if it's something as simple as formatting (e.g. white text so you simply cannot see it) but it's always worth a shot before looking elsewhere

The other option is, does Google Sheets have a toggle between automatic calculation & manual calculation (similar to Excel)? I honestly don't know, this is the first time I've looked at Google Sheets for any length of time! I end up using Excel Online from home instead.

Has it copied over as text rather than numbers?