IF VLOOKUP delimma with partial cell values

Okay, I have a problem with an IF VLOOKUP statement, one that I thought would be handled by setting the range lookup to true (or leaving it blank). It does not. I want to have the formula check in a cell to see if it contains the contents of another cell. The cell it is checking may have other values in it in addition to the contents being looked up. The IF part comes in where IF the formula finds the contents within that cell, it replicates that value. If not, then it says "no". Here's a version of the formula:

=IF(VLOOKUP(A15,blist,2)=A20,A20,"no")

- A15 contains a person's name (Meg, in this case)
- blist is the table array
- 2 is the column index number I want to check
- The contents of B8 (the col_index_number cell): blee blow blah
- Leaving the range lookup blank (or set to TRUE) should find an inexact match

A20 contains a term ("Blah", in this case)
IF true, then it displays the contents of A20: "Blah"
IF false, then it displays "no"

Well, it only displays "no", unless I *only* have the value "Blah" in the cell. Any other value dumps the formula. I want the formula to look for "Blah" even if "Blee" and "Blow" are in there with it. What am I doing wrong?

Okay, I re-ordered the text in the cell to read blah blee blow, and I get the same result. I even reversed it just in case (blow blee blah). Same result.

Is there, perhaps, another method to accomplish what I want?

Sorry. You were too fast. Read the edited version.
Ola

In your example:
Ann, blee
Bob, blow
Cindy, blah

I do not understand the above. Please elaborate. Does this refer to the index value versus the index number. I'm sorry.

As a note, if I remove the IF statement, and just have it VLOOKUP, it spits back the contents of the cell, which is fine. All I really want it to do is that when that happens, it gives me a specific result (the name I was looking for), and if it doesn't, that it says "no".

Hmm, I assumed that:

=IF(VLOOKUP(A15,blist,2)=A20,A20,"no")
blist:
Col A...ColB
Ann.... blee
Bob.... blow
Cindy...blah
Mary....bluu
...

Example1:
A15: Mary
A20: Blah
=IF(VLOOKUP("Mary",blist,2)="Blah","Blah","no")
---> "no"

Example2:
A15: Cindy
A20: Blah
=IF(VLOOKUP("Cindy",blist,2)="Blah","Blah","no")
---> "Blah"


Ola

Originally Posted by olasa

Example1:
A15: Mary
A20: Blah
=IF(VLOOKUP("Mary",blist,2)="Blah","Blah","no")
---> "no"

Example2:
A15: Cindy
A20: Blah
=IF(VLOOKUP("Cindy",blist,2)="Blah","Blah","no")
---> "Blah"


Ola

It's still giving me "no", even when the leftmost column all matches up. Is it because it returns the whole value of the cell, so that when the ="Blah" is checked (back to the IF statement), it gets dumped? Is there a way for the lookup result (the whole cell) to be then checked against the desired result ("Blah")? I mean, that seems like what I am doing, and what you are suggesting, but it is still reading as false.

Okay - Messing around with it clarified some things. It is not the VLOOKUP that is the problem at all. It is the IF statement. I'll work on it tonight...

It's getting late here but
- If XL return the right result from the Vlookup and
- If there are no extra " " (blanks) "Blah " v.s. "Blah"
It should work with the rest of the formula

However, I must admit, I'm not sure I understand... "...the whole value of the cell..." and "...the lookup result (the whole cell)...". I might be missing something important...?

Last try...since I have trouble understand some parts of the problem...


"What I want, however, is for XL to simply determine if "blah" is *anywhere* in that cell, no matter *what else* is in the cell with it. "

This might help:
http://www.excelforum.com/showthread.php?t=348161
or
=IF(COUNTIF(Range,"*blah*")>0,"Found","Nothing")

Ola Sandstrom

Good to hear.
Thanks.
Ola