Can we use multiple if with VLookup function

[Guest]

Need some help.

I have to extract multiple words from the string using VLookup function.

(Example :"Cell A1 "This is the best example")

Example : Finding "This" from cell A1- result VLookup (Sheet1) A112
Finding "IS" from cell A1- result VLookup (Sheet2) A120
Finding "BEST" from cell A1- result "Excellect"
Need to haev function for this

Thanks,

 

[Guest]

your post doesnt make sense to me but this formula is my interpretation of
your criteria.

 

[Guest]

Thanks!!!
Yes, it is bit confusing. Actually I want to use Vlookup function with IF
statement.
Please let me know in case i need to send some more information.
Thanks again.

 

[Pete_UK]

I also don't understand what it is that you want to do. However, you
might be able to make use of something like this:

=IF(condition_1,vlookup_1,IF(condition_2,vlookup_2,"neither of these"))

Hope you can make sense of that. <g>

Pete

 

[Guest]

Thanks Pete!!!
Actually I have to pickup a word from the string. Example("This is the best").
Now the condition is if the word is "the" than we need to refer to the list
of items, So i believe Vllokup function will be used.

If you can send accross your e-mail id than i'll be able to send the excel
sheet with all the details.

Hope this thing works.

Thanks!!!

 

[Pete_UK]

If you want to check if the word "the" appears in cell A1, you could
use this:

=IF(ISNUMBER(FIND("the",A1)),"yes","no")

FIND is case-sensitive - you might like to use SEARCH instead if case
is not important. Instead of "yes" here, you could have your VLOOKUP
function, and instead of "no" you could have another similar formula to
look for a second word and do something else if that word is present.
Is this something you can work with?

Hope this helps.

Pete

 

[Harlan Grove]

Pete_UK wrote...

If you want to check if the word "the" appears in cell A1, you could
use this:

=IF(ISNUMBER(FIND("the",A1)),"yes","no")

....

Picky: this only tests for the SUBSTRING "the" in A1. In addition to
the word 'the' it'd also match their, then, another etc. To check for
the word 'the' in a cell requires something a bit more elaborate.

=IF(COUNT(SEARCH(MID(" "&A1&" ",FIND("the",A1)+{0,4},1),
"ABCDEFGHIJKLMNOPQRSTUVWXYZ_0123456789"))=0,"yes","no")

This defines 'word' as any token containing letters, numbers or
underscores. Any other characters delimit 'words'.

 

[Guest]

I think i am very close now.

Could you please let me know how to use Multiple IF's in the same example

=IF(COUNT(SEARCH(MID(" "&A1&" ",FIND("the",A1)+{0,4},1),

"ABCDEFGHIJKLMNOPQRSTUVWXYZ_0123456789"))=0,"yes","no")

(This is a good example)

 

[Harlan Grove]

Multiple IF''''s with Vlookup wrote...

I think i am very close now.

Could you please let me know how to use Multiple IF's in the same example

....

No.

My response was specifically to Pete_UK. His formula didn't do what he
claimed it did.

I have no idea what you're trying to do because you haven't yet managed
to provide a cogent explanation. Until you do so, you're on your own.

 

[Pete_UK]

Harlan,

thanks for the elaboration. I think the following would suffice for
what the OP wants:

=IF(ISNUMBER(SEARCH(" the "," "&A1&" ")),"yes","no")

Pete

 

[Pete_UK]

You now have 3 people who say they don't really understand what you
want to do - can you explain things a bit more clearly? For example, if
you do have the word "the" in A1, what do you want to do with it? What
should happen if "the" is not present? What is the "list" that you
refer to? We can't really help you if we don't understand what the
problem is !!

Pete

 

[Guest]

This is the formula which I have:

=MID(L2,SEARCH(" vdi",L2)+1,4)

Now, I want to use multiple if statement in this formula.

We can work on Vlokup later.

Thanks!!!

 

[Harlan Grove]

Multiple IF''''s with Vlookup wrote...

This is the formula which I have:

=MID(L2,SEARCH(" vdi",L2)+1,4)

Now, I want to use multiple if statement in this formula.

....

I'm going to guess (sadly it's just a guess because you haven't yet
managed to be specific) you want to find the first of several possible
words in cell L2. I'll use the following sample array of words:
{"one","two","three","four"}. The first of these found in L2 would be
given by the formula

=INDEX({"one","two","three","four","none found"},MATCH(TRUE,
ISNUMBER(SEARCH(" "&{"one","two","three","four","*"}&" "," "&L2&"
")),0))

There's no need to use nested IF calls.