Index match fail - possibly format issue?!

[rayted]

Hi all,

I have 2 files

In file 1: EH_addin I am trying to write an index match formula to return in cell P4 =INDEX(EH_addinmas.csv!$J:$J,MATCH(C4,EH_addinmas.csv!$E:$E,FALSE)) to say cell C4 look at EH_addinmas and match column E and return data from column J.

it Returns N/A

I changed format from number in EH_addin to General still no difference

Any idea why or what is wrong?

thanks!

[Richard Buttrey]

Hi,

I suggest you open the csv and then save it as a.xlsx

Your MATCH formula is in any case referencing colmn E:E when it should be column A:A
And since there's nothing in column J the INDEX should presumably be referencing column F:F

Then use

=INDEX(EH_addinmas.xlsx!$F:$F,MATCH(C4,EH_addinmas.xlsx!$A:$A,FALSE),1)

[Undo]

Please Login or Register  to view this content.

You have to open both files and do the formula also one file numbers start with '
remove it and keep it numbers only

[rayted]

Hi both,

kept the files open
changed these to number format
still getting#n/a (even removed the apostophre in front)

[Richard Buttrey]

With the EH_addinmas file open, try the formula I gave in #2 but wrap the C4 in a TEXT function. i.e.

=INDEX([EH_addinmas.xlsx]EH_addinmas!$F:$F,MATCH(TEXT(C4,0),[EH_addinmas.xlsx]EH_addinmas!$A:$A,FALSE),1)

[rayted]

nOPE STILL no difference unfortunately - still N/A error..

[Glenn Kennedy]

In the CSV file, select column A. Text to columns, delimited, with ' as the separator. Then delete column A.

The formula then works... but the results are in column E... not column J.

[rayted]

THANKS Glenn and all, the text tio columns helped!