Remember Me?

#1
November 1st 04, 03:57 AM
 alex Posts: n/a
vlookup to see 2 values?

Hi..

Normally for vlookup u would look for a single value. now
i want to use 2 cells to do a vlookup.
i tried looking on net ... i found something using index
and match. i didnt clear understand it.pls do tell me the
easier way out or maybe i am using a wrond approach

pls suggest
thanks

#2
November 1st 04, 04:39 AM
 Domenic Posts: n/a

Try the following array formula, entered using CONTROL+SHIFT+ENTER...

=INDEX(RangeC,MATCH(1,(RangeA="First Lookup Value")*(RangeB="Second
Lookup Value"),0))

Note that if your lookup value is numerical, remove the quotes.

Hope this helps!

--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785

#3
November 1st 04, 04:48 AM
 Don Posts: n/a

Alex,

To match both "Joe" in Range1 and "Jones" in Range2, and return the value in
"ValueRange"

=INDEX(ValueRange, MATCH(1,(Range2="Jones")*(Range1="Joe"),0),0)

Enter as an array formula

"alex" wrote in message
...

Hi..

Normally for vlookup u would look for a single value. now
i want to use 2 cells to do a vlookup.
i tried looking on net ... i found something using index
and match. i didnt clear understand it.pls do tell me the
easier way out or maybe i am using a wrond approach

pls suggest
thanks

#4
November 1st 04, 10:22 AM
 Posts: n/a

hi thanks for

i tired it and it works but one problem:
here is my data
Brian Red Fire Water
Brian Blue Water Water
John red volcano Water
john blue hurricane Water

the last column is my value i got thru :
=INDEX(C1:C4,MATCH(1,(A1:A4="Brian")*(B1:B4="Blue" ),0),0)

all the values in last column comes out to be water? how
is it possible it should be water only for the 2nd case
for rest it shoudl be #n/a's??????//

am i doing anythign worng?

once again thanks a plenty

-----Original Message-----

Try the following array formula, entered using

CONTROL+SHIFT+ENTER...

=INDEX(RangeC,MATCH(1,(RangeA="First Lookup Value")*

(RangeB="Second
Lookup Value"),0))

Note that if your lookup value is numerical, remove the

quotes.

Hope this helps!

--
Domenic
----------------------------------------------------------

--------------
Domenic's Profile: http://www.excelforum.com/member.php?

action=getinfo&userid=10785

.

#5
November 1st 04, 11:32 AM
 Alex Delamain Posts: n/a

From your last post I am guessing that column D contains your lookup
formula which has been array entered and copied down the list of data.

If this is the case then it will give "Water" as the result every time
because it performs the same calculation on the whole data list every
time. The idea behind using an array formula is that a single cell can
check a whole list of data without having to be copied down all the
rows

If you intend to copy the formula down the data in another column then
the formula could be

=IF(AND(A2="Brian",B2="blue"),+C2,"")

--
Alex Delamain
------------------------------------------------------------------------
Alex Delamain's Profile: http://www.excelforum.com/member.php...o&userid=11273

#6
November 1st 04, 12:11 PM
 MikeY Posts: n/a

D1: {=INDEX(C\$1:C\$4,MATCH(1,(A\$1:A\$4=A1)*(B\$1:B\$4=B1), 0),1)}

Your wrong formula must be equivalent to;
{=INDEX(C\$1:C\$4,MATCH(1,(A\$1:A\$4=A\$1)*(B\$1:B\$4=B\$2 ),0),1)}

--
Mike

wrote in message ...
#7
November 2nd 04, 04:20 AM
 alex Posts: n/a

no htis is not what i want... i am too confused.
my simple requirement is i want to do a vlookup with 2
column values rather than with one value.

pls suggest if its feasible.
thanks again
alex
-----Original Message-----

From your last post I am guessing that column D contains

formula which has been array entered and copied down the

list of data.

If this is the case then it will give "Water" as the

result every time
because it performs the same calculation on the whole

data list every
time. The idea behind using an array formula is that a

single cell can
check a whole list of data without having to be copied

down all the
rows

If you intend to copy the formula down the data in

another column then
the formula could be

=IF(AND(A2="Brian",B2="blue"),+C2,"")

--
Alex Delamain
----------------------------------------------------------

--------------
Alex Delamain's Profile:

http://www.excelforum.com/member.php?
action=getinfo&userid=11273

.

#8
November 2nd 04, 04:35 AM
 Peo Sjoblom Posts: n/a

It's still not clear, if you want to return the values from 2 columns like
is in A3 and you want to return B3 and C3, then you need to select an array
and array enter
the formula

=VLOOKUP(lookup_value,A1:C100,{2,3},0)

to be entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

"alex" wrote in message
...
no htis is not what i want... i am too confused.
my simple requirement is i want to do a vlookup with 2
column values rather than with one value.

pls suggest if its feasible.
thanks again
alex
-----Original Message-----

From your last post I am guessing that column D contains

formula which has been array entered and copied down the

list of data.

If this is the case then it will give "Water" as the

result every time
because it performs the same calculation on the whole

data list every
time. The idea behind using an array formula is that a

single cell can
check a whole list of data without having to be copied

down all the
rows

If you intend to copy the formula down the data in

another column then
the formula could be

=IF(AND(A2="Brian",B2="blue"),+C2,"")

--
Alex Delamain
----------------------------------------------------------

--------------
Alex Delamain's Profile:

http://www.excelforum.com/member.php?
action=getinfo&userid=11273

.

#9
November 2nd 04, 08:47 AM
 Posts: n/a

my lookup value is not a single value but i want 2 values
to be the "lookup_value"!
-----Original Message-----
It's still not clear, if you want to return the values

from 2 columns like
is in A3 and you want to return B3 and C3, then you need

to select an array
and array enter
the formula

=VLOOKUP(lookup_value,A1:C100,{2,3},0)

to be entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

"alex" wrote in

message
...
no htis is not what i want... i am too confused.
my simple requirement is i want to do a vlookup with 2
column values rather than with one value.

pls suggest if its feasible.
thanks again
alex
-----Original Message-----

From your last post I am guessing that column D contains

formula which has been array entered and copied down the

list of data.

If this is the case then it will give "Water" as the

result every time
because it performs the same calculation on the whole

data list every
time. The idea behind using an array formula is that a

single cell can
check a whole list of data without having to be copied

down all the
rows

If you intend to copy the formula down the data in

another column then
the formula could be

=IF(AND(A2="Brian",B2="blue"),+C2,"")

--
Alex Delamain
--------------------------------------------------------

--
--------------
Alex Delamain's Profile:

http://www.excelforum.com/member.php?
action=getinfo&userid=11273

.

.

#10
November 2nd 04, 12:22 PM
 Domenic Posts: n/a

my lookup value is not a single value but i want 2 values
to be the "lookup_value"!

Can you provide a sample of your data?

--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Abhijeet Excel Discussion (Misc queries) 4 May 19th 05 04:30 AM JackRnl Charts and Charting in Excel 1 January 20th 05 02:04 AM Joelle_Smith Charts and Charting in Excel 1 December 23rd 04 08:58 PM Tom Excel Discussion (Misc queries) 4 December 2nd 04 01:44 AM ExcelHelp Excel Worksheet Functions 1 October 27th 04 07:03 PM

All times are GMT +1. The time now is 09:07 AM.