Lookup #N/A Error

[hokiedrum]

I am dealing with a large amount of data that comes to me in a specific format from my instrument. Without too many details, I am writing a spreadsheet that I can copy and paste the data (directly from the instrument) into and automatically perform an analysis of variance.

The data from the instrument is formatted in vertical columns, with each column being a different set and ~200 sets of data per run (the entire run is pasted into my sheet). In another sheet, where the ANOVA is being performed, each ANOVA has a cell next to it which is set equal to one of the cells across the row identifying each column of data. I am then using this cell for the LOOKUP function to pull the data into the ANOVA. For only one of the columns, every lookup returns an #N/A error. It is the same column regardless of the order (A-Z or not). If I remove the space in the middle of the column name, it then pulls the values correctly. However, every other column has a space in the middle of its name and they work. It isn't efficient for me to have to change the name of this column for each run. Does anyone have any idea why this lookup won't work (I can paste some dummy data if need be)?

I appreciate any help.

[Bob Phillips]

Post a dummy workbook that shows the failure.

[hokiedrum]

anova test.xlsx

There's a dummy, kind of cut down but the error still shows itself.

[Bob Phillips]

The data in lookup_vector for the LOOKUP must be in order yours isn't.

You could either use

=LOOKUP($F4,Data!C1:D1,Data!C14:D14)

or

=INDEX(Data!C14:D14,MATCH($F4,Data!C1:D1,0))

[JosephP]

remove the word Type from B1 on the data sheet. LOOKUP expects sorted data.

[hokiedrum]

Both of these make sense... but my question now is, why does it work for every other column when all 200 of them are there? Ag 243.779 is the only one that it doesn't work for, even if it is somewhere in the middle of all of the columns.

EDIT: Also, A1 normally has 'Sample' in it, but the data sorts properly without 'Type' in B1 yet with 'Sample' still in A1. Why is this? At this point, the problem is fixed but I'm just looking for an answer to better understand how it works for my own future reference.

[JosephP]

it's down to the way binary searches work. it would be quite surprising if only that column never works. if you have data in that row that should not be part of the lookup, do not include it in the formula lookup range.

[hokiedrum]

Yeah, I'm not sure why that's the only one. Thanks for the help though! Thread marked as solved.