Value errors in Match function

[Cuilmoss]

I recently exported two reports from Quickbooks Pro in CSV format. Text
strings in one report had surplus characters which I "removed" using text
functions. The resultant text using LEFT(no of characters) then matched text
strings in second report. The number of rows in the first report was about
twice those in second report. My aim was to obtain data in column 3 of the
second report by placing an Index and Match function in the first report
column 4. Whilst the first row worked, all subsequent rows produced the N#A
error. On evaluation the formula, the problem was no value in the array part
of the MATCH function. I suspect this might be something to do with CSV files
not being properly converted on opeing in Excel or that the computed text
string in report one cannot be used in a MATCH function yet the look up value
is correct. This is very frustrating as there are a lot of rows to deal with
(stock values)
Any help please....

 

[Toppers]

Without access to the data it's difficult to identify the cause of your
problem.

And what exactly are the formulae giving the problem?

 

[Cuilmoss]

Thanks for coming back. I attach an extract of my data. You will see that E11
computes the correct figure but for some reason all the remaining cells in
colE come up with #N/A yet E16 should be 46.

Cuilmoss

 

[Cuilmoss]

Sorry didn't include attachment (Excel file). How can I do that?

Cuilmoss

 

[Ron Rosenfeld]

I recently exported two reports from Quickbooks Pro in CSV format. Text
strings in one report had surplus characters which I "removed" using text
functions. The resultant text using LEFT(no of characters) then matched text
strings in second report. The number of rows in the first report was about
twice those in second report. My aim was to obtain data in column 3 of the
second report by placing an Index and Match function in the first report
column 4. Whilst the first row worked, all subsequent rows produced the N#A
error. On evaluation the formula, the problem was no value in the array part
of the MATCH function. I suspect this might be something to do with CSV files
not being properly converted on opeing in Excel or that the computed text
string in report one cannot be used in a MATCH function yet the look up value
is correct. This is very frustrating as there are a lot of rows to deal with
(stock values)
Any help please....

What you describe is certainly possible in Excel. Knowing neither the formula
that you are using (the one that gives the NA error) nor having examples of the
data make troubleshooting difficult.

Either your formula is incorrect (e.g. referring to the wrong range), or your
source and lookup values do not agree.

By the way, in your heading you indicate you are getting a "Value" error. In
the body you are indicating that you are getting an "NA" error. Which is it?

My brief discussion assumes the NA error.


--ron

 

[Toppers]

You can't add attachments here. Can you give a small sample in the posting
plus the formulae? (Also see reply from Ron)