Can Sumproduct be modified to return text?

[hbgray2]

Can the sumproduct formula be modified to return text? Here is my formula:
AM5 = part number
BOM!AM5:AM300 = list of part numbers (unsorted random data)
BOM!AN5:AN300 = list of part names
=SUMPRODUCT(--(AM5=BOM!AM5:AM300),BOM!AN5:AN300)
returns 0

=SUMPRODUCT(--(AM5=BOM!AM5:AM300))
returns 1 verifying there is only 1 match...

 

[Jonmo1]

This is pretty much what Vlookup or index/match is for...

=VLOOKUP(AM5,BOM!AM5:AN300,2,FALSE)

I prefer Index/Match

=INDEX(BOM!AN5:AN300,MATCH(AM5,BOM!AM5:AM300,0))


Hope that helps.

 

[T. Valko]

Can the sumproduct formula be modified to return text? Here is my formula:
AM5 = part number
BOM!AM5:AM300 = list of part numbers (unsorted random data)
BOM!AN5:AN300 = list of part names
=SUMPRODUCT(--(AM5=BOM!AM5:AM300),BOM!AN5:AN300)
returns 0

=SUMPRODUCT(--(AM5=BOM!AM5:AM300))
returns 1 verifying there is only 1 match...

SUMPRODUCT only returns numeric values (or errors).

 

[hbgray2]

This is pretty much what Vlookup or index/match is for...

=VLOOKUP(AM5,BOM!AM5:AN300,2,FALSE)

I prefer Index/Match

=INDEX(BOM!AN5:AN300,MATCH(AM5,BOM!AM5:AM300,0))


Hope that helps.

Thanks for the help. The Index/Match formula did the trick. I forgot about that one and was unsuccessfully trying the Vlookup