IFCONTAINS?

Posted by Jerry on October 10, 2001 8:26 AM

I'm looking for a way to basically have a formula that is able to look at cell with these possible values:

A
1 JB X##
2 HW X##
3 DI X##
4 CB X##
5 GI X##

(Where X is a letter and ## is a number)

and compare the first two letters and lookthem up in a table like so:

B C
1 JB $500
2 HW $300
3 DI $200
4 CB $600
5 GI $700

Something like:

=IFCONTAINS(A1firsttwoletters,LOOKUP(A1firsttwoletters,B1:C5),0)

Thanks for your help.

Jerry

Posted by Aladin Akyurek on October 10, 2001 8:36 AM

Jerry,

Try:

=IF(COUNTIF($B$1:$B$100,LEFT(A1,2)),VLOOKUP(LEFT(A1,2),$B$1:$C$100,2,0),"")

This formula returns a blank if a 2-letter lookup-value is not available in B1:C100 (the lookup table).

Aladin

==========

Posted by Jerry on October 10, 2001 11:52 AM

Perfect! Thank you so much! (No msg)