Index, Match limitations

[SFCChase]

Is there a limitation to the size of table that can be index/matched?

I have a workbook with multiple tabs. 1 tab is solely composed of tables of data. Another tab (labeled ACFT Table) uses entered data to run a search of the data tables. The following formula works perfectly and returns correct results

=IF(F8="M",INDEX('ACFT Table'!$C$4:$L$32,MATCH($H8,'ACFT Table'!$B$4:$B$32,0),MATCH(E8,'ACFT Table'!$C$2:$L$2,1)),IF(F8="","","")))

When I change the search variables and table locations I get an #NA error.

=IF(F8="M",INDEX('ACFT Table'!$O$4:$X$128,MATCH($H8,'ACFT Table'!$N$4:$N$128,0),MATCH(E8,'ACFT Table'!$N$2:$X$2,1)),IF(F8="","","")))

I have found that if i enter data between lines 4 and 68 i will get a result, but anything past 69 gives me the #NA error.

Any suggestions would be great.

 

[StephenCrump]

No, it's not a size problem.

When you get the error, check your two MATCH values, i.e. MATCH($H8,'ACFT Table'!$N$4:$N$128,) and MATCH(E8,'ACFT Table'!$N$2:$X$2,1)

You should find that one (or both) have value #N/A, i.e. the match wasn't found?

Incidentally, your second match has match_type = 1. Is this what you intended? Also, if you do get a result it will be one column out, because you're indexing on O:X but matching on N:X

 

[SFCChase]

No, it's not a size problem.

When you get the error, check your two MATCH values, i.e. MATCH($H8,'ACFT Table'!$N$4:$N$128,) and MATCH(E8,'ACFT Table'!$N$2:$X$2,1)

You should find that one (or both) have value #N/A, i.e. the match wasn't found?

Incidentally, your second match has match_type = 1. Is this what you intended? Also, if you do get a result it will be one column out, because you're indexing on O:X but matching on N:X

The Match being a column over is intentional. The value I'm looking for is in Column N but the data table itself starts in Column O.

The N/A error is showing up in my first MATCH portion, but only if i search for a value found below row 68.

 

[StephenCrump]

The N/A error is showing up in my first MATCH portion, but only if i search for a value found below row 68.

If: =MATCH($H8,'ACFT Table'!$N$4:$N$128,) returns #N/A, and you think there is a match in 'ACFT Table'!N69, say,

Then if you test: =H8='ACFT Table'!N69, you should find it returns FALSE, i.e. it's not actually a match.

This might be because:
- You're comparing text values, and there are space differences, e.g. leading or trailing space characters
- You're comparing numbers, and some of your "numbers" are actually text values

 

[SFCChase]

If: =MATCH($H8,'ACFT Table'!$N$4:$N$128,) returns #N/A, and you think there is a match in 'ACFT Table'!N69, say,

Then if you test: =H8='ACFT Table'!N69, you should find it returns FALSE, i.e. it's not actually a match.

This might be because:
- You're comparing text values, and there are space differences, e.g. leading or trailing space characters
- You're comparing numbers, and some of your "numbers" are actually text value

This is driving me insane....it is returning a FALSE value for any row below N68 but i have gone into formatting on the data table, column N, and the input variable on H8 and all are set to Number with 1 decimal point. I don't understand how it's returning a FALSE

 

[SFCChase]

This is driving me insane....it is returning a FALSE value for any row below N68 but i have gone into formatting on the data table, column N, and the input variable on H8 and all are set to Number with 1 decimal point. I don't understand how it's returning a FALSE

So to make things even more confusing....i went line by line with the =h8=n# for every number that is in the N column (runs from 3.5 up to 16.0 in 0.1 increments) and found that all input values between 6.0 and 10.0 return a FALSE and everything else is TRUE....I'm so confused

 

[AhoyNC]

Using Format Cells will not change a cell from text to a number.

Try this to change text to numbers.
Select your column N
Goto

1. Data
2. Text to Columns
3. Next
4. Next
5. Your 3rd screen should look like below (make sure General is checked)
6. Finish

 

[StephenCrump]

Try this to change text to numbers ...

... or if you like keyboard shortcuts: ALT+D, E, F

 

[SFCChase]

... or if you like keyboard shortcuts: ALT+D, E, F

So I ended up going through every line of my table column (column N) and it looks like for some reason when i used a drag to fill in values it sporadically would change them but still display whole numbers.

example: I drug down from 10.0, 9.9, 9.8 etc....to 3.0. For some reason instead of 8.2 being entered it was put in as 8.19999978 or 8.20000001. That was giving me my FALSE error. No idea why it happened, but i went in and manually entered each value and now it seems to be working again.

thanks for the help

 

[StephenCrump]

I'm glad you managed to fix the problem.

No idea why it happened ....

Floating-point arithmetic may give inaccurate result in Excel - Office