indexing to find an alpha numaric entry

[Jing]

I am useing the following formula:
=IF(ROWS(C$4:C4)>$I$1,"",INDEX(Equipment!$D:$D,SMALL(IF(Equipment!$C:$C=$A$2,ROW(Equipment!$C:$C)),ROWS(C$4:C4))))

but when i use it to find an alphanumaric entry in C:C it comes back with an Num error... most likly due to teh letters...

just wondering if there is a way to fix that so it will report the correct value...

Thanks

 

[Jing]

Sorry copied the wrong formula

=IF(ROWS(C$4:C4)>$I$1,"",INDEX(Equipment!$A:$A,SMALL(IF(Equipment!$B$1:$B$2375=$A$2,ROW(Equipment!$B$1:$B$2375)),ROWS(C$4:C4))))

 

[T. Valko]

Sorry copied the wrong formula

=IF(ROWS(C$4:C4)>$I$1,"",INDEX(Equipment!$A:$A,SMALL(IF(Equipment!$B$1:$B$2375=$A$2,ROW(Equipment!$B$1:$B$2375)),ROWS(C$4:C4))))

I'm assuming there's a count formula in I1?

That's where the problem would be. The formula checks to see if the data in column B matches the lookup value in A2. It either matches or it doesn't.

If the count function is counting things that aren't exact matches then that will lead to problems.

So, we'd need to see some sample data to figure out where this is going wrong.

 

[Jing]

your right there is a count in I1 and it works it counts the correct amount based on the information entered into A2... I just get NUM error as a result...

 

[T. Valko]

your right there is a count in I1 and it works it counts the correct amount based on the information entered into A2... I just get NUM error as a result...

Let's assume the count formula in I1 returns 5.

The logical test in the IF function:

...IF(Equipment!$B$1:$B$2375=$A$2...

Only has 4 matches. So, that's how the formula ends up returning the #NUM! error. The count formula must return the same number of matches as the IF logical test.

Without knowing what the data looks like or what the count formula looks like there's no way I can tell you where the problem is.