Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup 2 conditiona
Is it possible to use vlookup to return a value if a second cell meets
a certain condition? For example A1 -1 B2-2 C3-3 D4-4 H1-? A2- 1 B2-7 C2-8 D2-9 I want to vlookup the number 1 in column A and return the value in column cell H1 where column D has a value of 9 In this case it would return the number 9 Or is the some other way to do this Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup 2 conditiona
One way for multi-condition lookup is to use index/match (array-entered),
something along these lines .. Put in the formula bar for H1, then array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =INDEX(A1:A100,MATCH(1,(A1:A100=1)*(D1:D100=9),0)) Adjust the ranges to suit (note that entire col references cannot be used) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Little Penny" wrote in message ... Is it possible to use vlookup to return a value if a second cell meets a certain condition? For example A1 -1 B2-2 C3-3 D4-4 H1-? A2- 1 B2-7 C2-8 D2-9 I want to vlookup the number 1 in column A and return the value in column cell H1 where column D has a value of 9 In this case it would return the number 9 Or is the some other way to do this Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup 2 conditiona
Thanks for your reply I do not quite understand you answer but with my lack of experience it's probably the way I asked the question. I'll try to make a little clearer. A B C D H L 1 1 2 3 4 ? 1 2 1 5 8 6 3 1 7 4 12 =VLOOKUP(L1,A:D,3,0) I want this formula to return value to H1 only if cell D in it row is 12. In this case it should return a value of 4. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup 2 conditiona
Let's say you have entered:
In L1: 1 (value for col A) In M1: 12 (value for col D) Then place in H1, and array-enter the formula (ie press CTRL+SHIFT+ENTER): =INDEX($C$1:$C$100,MATCH(1,($A$1:$A$100=L1)*($D$1: $D$100=M1),0)) For the sample data posted, H1 will return 4. As-is, if you have other pairs of values for cols A and D listed down in L2:M2, L3:M3, etc, then you could just copy the formula in H1 down (with the ranges adjusted to suit). -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Little Penny" wrote in message ... Thanks for your reply I do not quite understand you answer but with my lack of experience it's probably the way I asked the question. I'll try to make a little clearer. A B C D H L 1 1 2 3 4 ? 1 2 1 5 8 6 3 1 7 4 12 =VLOOKUP(L1,A:D,3,0) I want this formula to return value to H1 only if cell D in it row is 12. In this case it should return a value of 4. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup 2 conditiona
Let me get to what I'm really trying to get at..
I have a workbook with 2 sheets On sheet 1 I use the following vlookup formula =VLOOKUP(E4,Sheet2!A:C,3,0) On "sheet 2" the value of E4 may appear more than once in column "A". The deciding factor is cell D on "sheet 2" in the array which is a date if its equal to the date in column I in the same row as E4 on "sheet 1" that's the value I want. Example If E4=excel. We go to sheet 2 column A and look for excel. Get value from 3rd column. What I want to do is have the formula only return the value if cell D in the row it finds E4 is equal to I4 on sheet one wow Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup 2 conditiona
In Sheet1,
Place in say, J4, then array-enter the formula (ie press CTRL+SHIFT+ENTER): =INDEX(Sheet2!$C$1:$C$100,MATCH(1,(Sheet2!$A$1:$A$ 100=E4)*(Sheet2!$D$1:$D$100=I4),0)) Correctly array-entered, Excel will wrap curly braces around the formula { }. Look out for these braces in the formula bar for J4 to confirm that you have array-entered. If you don't see it, then the formula hasn't been correctly array-entered. Adjust the ranges to suit (note that entire col refs cannot be used). -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Little Penny" wrote in message ... Let me get to what I'm really trying to get at.. I have a workbook with 2 sheets On sheet 1 I use the following vlookup formula =VLOOKUP(E4,Sheet2!A:C,3,0) On "sheet 2" the value of E4 may appear more than once in column "A". The deciding factor is cell D on "sheet 2" in the array which is a date if its equal to the date in column I in the same row as E4 on "sheet 1" that's the value I want. Example If E4=excel. We go to sheet 2 column A and look for excel. Get value from 3rd column. What I want to do is have the formula only return the value if cell D in the row it finds E4 is equal to I4 on sheet one wow Thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup 2 conditiona
Hey thanks for the help I know I'm getting close just can't get it to
work. This is my first time using index function. =INDEX(Sheet2!$C$1:$C$1405,MATCH(1,(Sheet2!$A$1:$A $1405=E4)*(Sheet2!$D$1:$D$1405=I4),0)) I get #NA and I think it should work because on sheet 2 A26=E4 and D26=I4 it should give me the value in cell C26 but I get #N/A in cell J4 of sheet 1 where I entered the formula. Any suggestions Thanks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup 2 conditiona
You probably didn't array-enter the adapted formula correctly ..
Try this: Click inside the formula bar for J4, then press CTRL+SHIFT+ENTER Done correctly, Excel will wrap curly braces around the formula { }. Look out for these braces in the formula bar for J4 to confirm that you have array-entered. If you don't see it, then the formula hasn't been correctly array-entered. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Little Penny" wrote in message ... Hey thanks for the help I know I'm getting close just can't get it to work. This is my first time using index function. =INDEX(Sheet2!$C$1:$C$1405,MATCH(1,(Sheet2!$A$1:$A $1405=E4)*(Sheet2!$D$1:$D$1405=I4),0)) I get #NA and I think it should work because on sheet 2 A26=E4 and D26=I4 it should give me the value in cell C26 but I get #N/A in cell J4 of sheet 1 where I entered the formula. Any suggestions Thanks |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup 2 conditiona
=INDEX(Sheet2!$C$1:$C$1405,MATCH(1,(Sheet2!$A$1:$A $1405=E4)*(Sheet2!$D$1:$D$1405=I4),0))
well .. if you did array-enter the above correctly earlier, and it still returned #N/A, then there's some data inconsistency somewhere throwing up the non-matches in either the lookup values in E4 and/or I4 versus the values within the corresponding lookup ranges: Sheet2!$A$1:$A$1405, Sheet2!$D$1:$D$1405 Try this revision to your formula in J4 (remember to array-enter): =INDEX(Sheet2!$C$1:$C$1405,MATCH(1,(TRIM(Sheet2!$A $1:$A$1405)=TRIM(E4))*(TRIM(Sheet2!$D$1:$D$1405)=T RIM(I4)),0)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Little Penny" wrote in message ... Hey thanks for the help I know I'm getting close just can't get it to work. This is my first time using index function. =INDEX(Sheet2!$C$1:$C$1405,MATCH(1,(Sheet2!$A$1:$A $1405=E4)*(Sheet2!$D$1:$D$1405=I4),0)) I get #NA and I think it should work because on sheet 2 A26=E4 and D26=I4 it should give me the value in cell C26 but I get #N/A in cell J4 of sheet 1 where I entered the formula. Any suggestions Thanks |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup 2 conditiona
Hey thanks for not given up on me I think your last reply is going to
work. I'm going to look at it closely later today at work. I will reply back to my original post. Thanks again MAX |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup 2 conditiona
MAX thanks for all your help the formula worked as advertised.
One last question is there a way to have excel say some in the cell instead of #NA if info is not found. =INDEX(Sheet2a!$C$1:$C$1405,MATCH(1,(TRIM(Sheet2a! $A$1:$A$1405)=TRIM(E4))*(TRIM(Sheet2a!$D$1:$D$1405 )=TRIM(I4)),0)) Like" Not Release" Thanks again |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup 2 conditiona
We can add an "IF(ISNA(),.. error trap in front, viz of the form:
=IF(ISNA(MATCH(..)),"Not Released",INDEX(...,MATCH(...)) So this should work for you (array-entered): =IF(ISNA(MATCH(1,(TRIM(Sheet2a!$A$1:$A$1405)=TRIM( E4))*(TRIM(Sheet2a!$D$1:$D$1405)=TRIM(I4)),0)),"No t Released",INDEX(Sheet2a!$C$1:$C$1405,MATCH(1,(TRIM (Sheet2a!$A$1:$A$1405)=TRIM(E4))*(TRIM(Sheet2a!$D$ 1:$D$1405)=TRIM(I4)),0))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Little Penny" wrote in message ... MAX thanks for all your help the formula worked as advertised. One last question is there a way to have excel say some in the cell instead of #NA if info is not found. =INDEX(Sheet2a!$C$1:$C$1405,MATCH(1,(TRIM(Sheet2a! $A$1:$A$1405)=TRIM(E4))*(TRIM(Sheet2a!$D$1:$D$1405 )=TRIM(I4)),0)) Like" Not Release" Thanks again |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup 2 conditiona
Max that work graet...
Thank you thank you thank you Little Penny |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup 2 conditiona
You're welcome !
Thanks for the feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Little Penny" wrote in message ... Max that work great... Thank you thank you thank you Little Penny |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP and its properties | Excel Worksheet Functions | |||
VLookup a Vlookup | Excel Worksheet Functions | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |