Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
alex
 
Posts: n/a
Default 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   Report Post  
Domenic
 
Posts: n/a
Default


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
View this thread: http://www.excelforum.com/showthread...hreadid=274073

  #3   Report Post  
Don
 
Posts: n/a
Default

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   Report Post  
 
Posts: n/a
Default

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
View this thread:

http://www.excelforum.com/showthread...hreadid=274073

.

  #5   Report Post  
Alex Delamain
 
Posts: n/a
Default


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
View this thread: http://www.excelforum.com/showthread...hreadid=274073



  #6   Report Post  
MikeY
 
Posts: n/a
Default

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   Report Post  
alex
 
Posts: n/a
Default

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

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?
action=getinfo&userid=11273
View this thread:

http://www.excelforum.com/showthread...hreadid=274073

.

  #8   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

It's still not clear, if you want to return the values from 2 columns like
if your lookup value
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

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?
action=getinfo&userid=11273
View this thread:

http://www.excelforum.com/showthread...hreadid=274073

.



  #9   Report Post  
 
Posts: n/a
Default

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
if your lookup value
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

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?
action=getinfo&userid=11273
View this thread:

http://www.excelforum.com/showthread...hreadid=274073

.



.

  #10   Report Post  
Domenic
 
Posts: n/a
Default


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
View this thread: http://www.excelforum.com/showthread...hreadid=274073



  #11   Report Post  
Alex Delamain
 
Posts: n/a
Default


The sample is already there.

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

The question is: How does he write a formula which does the following:
If column A is Brian and column B is Blue then the answer is the value
in column c (ie Water)

In Alex's earlier post he shows a column D which is Water, Water ,
Water, Water because he has entered the following formula
=INDEX(C1:C4,MATCH(1,(A1:A4="Brian")*(B1:B4="Blue" ),0),0)
and then copied it down for each row. The formula is working fine and
picking the correct answer each time but, because the same formula is
in D1 - D4 it returns the same answer in each row.

Alex - do you want the answer in a single cell?
If you do you already have a working formula. If you want to have a
fourth column which returns an answer for each row - ie N/A, N/A,
Water, N/A then my previous post holds true
in D1
=IF(AND(A1="Brian",B1="blue"),+C1,#N/A)

If neither of the above then please come back with a clear example of
what you want to achieve


--
Alex Delamain
------------------------------------------------------------------------
Alex Delamain's Profile: http://www.excelforum.com/member.php...o&userid=11273
View this thread: http://www.excelforum.com/showthread...hreadid=274073

  #12   Report Post  
Domenic
 
Posts: n/a
Default


Alex Delamain Wrote:
The sample is already there.


Oops, sorry! I missed part of the thread.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=274073

  #13   Report Post  
Sigmaz
 
Posts: n/a
Default

I am havina siliar problem however I have a dynamic sheet that is generated
by access looks like this:


A B C
COMPLETED By Day CODE_1 Multiplier
Friday, March 18, 2005 1 20
Monday, March 14, 2005
Saturday, March 19, 2005 1 25
Thursday, March 17, 2005 1 22
Thursday, March 17, 2005 4 1
Tuesday, March 15, 2005
Wednesday, March 16, 2005
Wednesday, March 16, 2005 1 1
Wednesday, March 16, 2005 4 1

Now this table can change from day to day depending on what the worker
does, So I need to beable to have another cell on another sheet come over
here and check on two cirteria,
1 Day of the week. i.e. Moday, then look to the job code and find the code 1
- 8 (job codes that are differant in price payout) then it can retrieve the
total of that job in column C to bring into the cell on the other sheet.
But because there can be multiple codes per day I need to have the formula
discriminate with the two lookup_values so I can make sure that the correct
data is being placed in the correct cell on the other page.
since this page is dynamic I guess I need to use =OFFSET to range the
formula?.. I dunno I just cant get it to work.


"Don" wrote:

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Zero values in a log chart Joelle_Smith Charts and Charting in Excel 2 May 8th 23 03:42 AM
how do i get mutiple values using vlookup in excel, lookup value . Abhijeet Excel Discussion (Misc queries) 4 May 19th 05 04:30 AM
Second serie doesn't use X-as values JackRnl Charts and Charting in Excel 1 January 20th 05 02:04 AM
Need help with modifying VLookUp Tom Excel Discussion (Misc queries) 4 December 2nd 04 01:44 AM
Need to sum values of columns 1 - 13 and 4 - 15 ExcelHelp Excel Worksheet Functions 1 October 27th 04 07:03 PM


All times are GMT +1. The time now is 03:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"