Anyone feeling kind?

[MrRobin]

BlasterBates is probably right, sometimes a spreadsheet can go haywire when it's had data imported into it from elsewhere. I've seen it a few times and it's completely unpredictable.

Best bet is to start a brand new workbook, then copy and paste values from your broken sheet into a sheet in the new workbook. Then try a new lookup formula in the new workbook and see what happens.

You could also try making a new column in your existing sheet and fill it with =value(A2) (if Col A contains your lookup IDs) and paste down and then do the lookup on that column instead.

You could also try a combination of INDEX and MATCH instead of VLOOKUP.

[LisaContractorUmbrella]

Thanks guys - I'll give rebuilding it a go and see how I get on. The data on the various sheets is all imported from other programmes so I think you may be right that it has just thrown a wobbly

[LisaContractorUmbrella]

Right tried that. I have come to the conclusion that it must be the data that's been imported that's causing the problem as a really simple Vlookup is not working in a completely new workbook. Some of the data is downloaded as a csv file from a banking system - do any of you know whether or not they would have built something in to stop the data being used???? Have tried =CLEAN(), =TRIM(), =LEN() and =ISNUMBER() on the data and everything seems to have worked - length is correct and number format is confirmed but am still getting #N/A

[BrilloPad]

Right tried that. I have come to the conclusion that it must be the data that's been imported that's causing the problem as a really simple Vlookup is not working in a completely new workbook. Some of the data is downloaded as a csv file from a banking system - do any of you know whether or not they would have built something in to stop the data being used???? Have tried =CLEAN(), =TRIM(), =LEN() and =ISNUMBER() on the data and everything seems to have worked - length is correct and number format is confirmed but am still getting #N/A

Can you narrow it down to a really simple example, change the data a bit so there are no privacy concerns then post it up here? Or maybe PM it to those who are interested - I am!

[oscarose]

Have you reference locked your arrays?

[LisaContractorUmbrella]

Have you reference locked your arrays?

Yep all good

[oscarose]

Yep all good

Are your numbers formatted as text?

[LisaContractorUmbrella]

All sorted - thanks for all the input guys and a big for Brillo who has just saved my sanity

[BrilloPad]

All resolved now - I am an excel god!

[oscarose]

All resolved now - I am an excel god!

Trailing spaces?