Data Import Advice (Formatting)

Hi all,

I have searched for this on Google and struggle to get a clear answer.

I am importing data into excel from another workbook using Data > Get Data > From File > Excel .xls

This then creates a data connection and then a new sheet with the imported data.

However, when I try to query this imported sheet with VLOOKUP or INDEX/MATCH or XLOOKUP etc, sometimes I get a "#N/A" error. I have established that this is due to some issue with the lookup value formatting. For example:

=IFERROR(VLOOKUP($A$1,'ImportedSheet'$A$1:$C$8,2,),"")

A1 is for instance a number = 26

In the imported sheet data, A4 for example is 26. However the VLOOKUP produces the N/A error.

When I change the lookup value to VLOOKUP("26",'ImportedSheet...) it works.

My guess is something to do with the formatting when imported. Can anyone help clarify this for me and advise on the best way of dealing with data in this way?

Thanks for your help!