Vlookup 2 conditiona

[Little Penny]

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

 

[Max]

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)*(D1100=9),0))

Adjust the ranges to suit (note that entire col references cannot be used)

 

[Little Penny]

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

 

[Max]

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

 

[Little Penny]

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

 

[Max]

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

 

[Little Penny]

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

 

[Max]

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]

=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)=TRIM(I4)),0))

 

[Little Penny]

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

 

[Little Penny]

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

 

[Max]

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)),"Not
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)))

 

[Little Penny]

Max that work graet...

Thank you thank you thank you


Little Penny

 

[Max]

You're welcome !
Thanks for the feedback ..