VLOOKUP with 2 conditions

[Brian Ferris]

Hi there,

Any ideas on how i can search based on two fields ?
Scenario is as follows:

Brian Red Fire
Brian Blue Water
john Red Volcano
John Blue Hurricane

I want to search For "Brian" which is equal to "Blue" and
the answer I want to obtain is "water"

Any ideas will be appreciated. I know you can concatenate
Brian & Blue & apply a vlookup but i know thios can be
done by the use of an array.

Thanks in advance.

Brian

 

[Frank Kabel]

Hi
try the following array formula
=INDEX(C1:C100,MATCH(1,(A1:A100="Brian")*(B1:B100="Blue"),0))

 

[Dave]

I needed the same sort of formula but I can't get Franks to work.... I tried
with Brians example and get #N/A in the cell where I place the formula

 

[Frank Kabel]

Hi
you have to enter this formula as array formula with CTRL+sHIFT+ENTER

 

[Dave]

thanks frank!

Hi
you have to enter this formula as array formula with CTRL+sHIFT+ENTER

 

[Dave Peterson]

Frank wrote to use an array formula:

This means that you hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

 

[Dave]

can't seem to get ctrl-shift-enter to worl either...... I copied (ctrl-C)
the formula, then tried as you directed, but it still says#N/A..... it puts
the curly brackets in place however

 

[Dave]

strangely... it works on my laptop and not on my desk top!
and I copied identically.....

can't seem to get ctrl-shift-enter to worl either...... I copied (ctrl-C)
the formula, then tried as you directed, but it still says#N/A..... it puts
the curly brackets in place however

Frank wrote to use an array formula:

This means that you hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula.

(don't

 

[Dave Peterson]

when you tried the ctrl-shift-enter, did you "edit" the cell first.

F2, then ctrl-shift-enter should be enough.

strangely... it works on my laptop and not on my desk top!
and I copied identically.....

 

[Dave]

I am completely mystified.....
I copied the original text from the first post...
Brian Red Fire
Brian Blue Water
john Red Volcano
John Blue Hurricane

pasted it to my excel sheet, did the text to columns so it would all be
neat, copied franks formula into E4, did the ctlr+shift+enter nope ....
tried F2 then ctrl+shift+enter..still says #N/A.
Some weird column thing must be going on.... It was working on excel on
the lap top,
but now I went back to try on the lap top again and it is not working ... so
I must have done something very lucky the first time!

 

[Dave Peterson]

Maybe it's a hardware (keyboard????) problem.

Gently bang the heck out of that laptop.

If you didn't break the laptop, did it work?

 

[Dave]

Thanks dave....
well it appears that when I pasted the info and then did the "text to
column" thing, something strange happens to those columns that the formula
doesn't like that I can't figure out. When I just type each individual
entry in
to the cells, it works............hmmmmm.

 

[Dave Peterson]

If you pasted the formula into the cell after you did a text to columns, you may
have been foiled by excel's memory--it likes to help out by doing an another
(unasked for) data|text to columns (of the stuff your pasting).

You could paste it directly in the formula bar and it should work.

Thanks dave....
well it appears that when I pasted the info and then did the "text to
column" thing, something strange happens to those columns that the formula
doesn't like that I can't figure out. When I just type each individual
entry in
to the cells, it works............hmmmmm.

 

[Dave]

nope..... that didn't do it..... weird huh?
I have to do some other work, but will get back to it in a while.....

 

[Dave]

AH HA..... I think what I was doing wrong was when i pasted, I had not done
"paste special" and it was going in HTML from the post where I copied?

I am completely mystified.....
I copied the original text from the first post...
Brian Red Fire
Brian Blue Water
john Red Volcano
John Blue Hurricane

pasted it to my excel sheet, did the text to columns so it would all be
neat, copied franks formula into E4, did the ctlr+shift+enter nope ....
tried F2 then ctrl+shift+enter..still says #N/A.
Some weird column thing must be going on.... It was working on excel on
the lap top,
but now I went back to try on the lap top again and it is not working ... so
I must have done something very lucky the first time!