Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]() 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
![]() |
|||
|
|||
![]() 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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]() 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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]() 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 |
Reply |
|
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
how do i get mutiple values using vlookup in excel, lookup value . | Excel Discussion (Misc queries) | |||
Second serie doesn't use X-as values | Charts and Charting in Excel | |||
Zero values in a log chart | Charts and Charting in Excel | |||
Need help with modifying VLookUp | Excel Discussion (Misc queries) | |||
Need to sum values of columns 1 - 13 and 4 - 15 | Excel Worksheet Functions |