VLOOKUP with 2 conditions

B

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
 
F

Frank Kabel

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

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
 
D

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.)
 
D

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
 
D

Dave

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



Dave said:
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 Peterson said:
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.
Click to expand...
(don't
Click to expand...
 
D

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.....
Click to expand...
 
D

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!
 
D

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?
 
D

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.
 
D

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.
Click to expand...
 
D

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.....
 
D

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?


Dave said:
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!
Click to expand...
 
Top