IF statement to return value when cell contains a specific word finding "1" for "10"

My IF statement should return a given value if a cell contains a specific word. The problem is the formula sees "DA-10" as "DA-1", and "DA-20" as "DA-2". I want the cell containing "DA-10" to return "7.02" but it is returning "4.42", the value for "DA-1", since the cell does in fact contain that string of text. I've tried it with and without wildcards but can't figure out how to make it differentiate between 1 and 10, 2 and 20, etc.

Please Login or Register  to view this content.

Hi there,
canīt it be done with VLOOKUP()?

Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Try this

=IFERROR(VLOOKUP(LEFT(C2,FIND(" ",C2)-1)&"*",$O$2:$P$17,2,0),VLOOKUP(C2,$O$2:$P$17,2,0))

or
=LOOKUP(10^99,SEARCH($P$2:$P$17,D2),$Q$2:$Q$17)
should work

Originally Posted by martindwilson

or
=LOOKUP(10^99,SEARCH($P$2:$P$17,D2),$Q$2:$Q$17)
should work

maybe like this?

=LOOKUP(10^99,SEARCH($O$2:$O$17,C2),$P$2:$P$17)

ah i inserted a column to test
=LOOKUP(10^99,SEARCH($O$2:$O$17,C2),$P$2:$P$17) is correct

Hi,

Instead of your very very BIG IF condition, you can use the below formula to fetch the values in column A.

If your Excel version is 2007 and above use,

Formula:

Please Login or Register  to view this content.

If your Excel version is prior to 2007 use,

Formula:

Please Login or Register  to view this content.

Copy down this formula for all rows and change the worksheet ranges as required.

You're welcome and thank you for the feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).