Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Vlookup With 2 Conditions

1. Senior Member
Join Date
29th December 2005
Posts
200

## Vlookup With 2 Conditions

=VLOOKUP(A2,MATT_INUP!A\$2:B\$10000,2,FALSE)
But I would also like it to make sure it finds the one that has a "0" in MATT_INUP!\$D\$2:\$D\$10000

I made the formula below, but it wont work. Im not sure why it looks good to me?!

=INDEX(MATT_INUP!\$A\$2:\$D\$10000,MATCH(1,(MATT_INUP!\$A\$2:\$A\$10000=A2)*(MATT_INUP!\$D\$2:\$D\$10000="0"),0),4)

btw...i did the ctl shift enter

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Vlookup With 2 Conditions

Is it a text "0" that you are searching for, if not it would need to look for numeric 0 - no quotes. Try:

=SUMPRODUCT((A2=MATT_INUP!A\$2:A\$10000)*(MATT_INUP!B\$2:B\$10000)*(0=MATT_INUP!D\$2:D\$10000))

or

=SUMPRODUCT((A2=MATT_INUP!A\$2:A\$10000)*(MATT_INUP!B\$2:B\$10000)*("0"=MATT_INUP!D\$2:D\$10000))

3. Senior Member
Join Date
29th December 2005
Posts
200

## Re: Vlookup With 2 Conditions

i still cant get that to work

i posted a quick little example....

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Vlookup With 2 Conditions

Sorry, SUMPRODUCT won't work, because column B is not numeric. So you do need to use INDEX/MATCH/MATCH.

5. Senior Member
Join Date
29th December 2005
Posts
200

## Re: Vlookup With 2 Conditions

ive been staring at this formula thinking of where to put the match/match...
yea i have no idea how to do that one.

=INDEX(MATT_INUP!\$A\$2:\$D\$10000,MATCH(1,(MATT_INUP!\$A\$2:\$A\$10000=A2)*(MATT_INUP!\$D\$2:\$D\$10000="0"),0),4)

now sure how to setup that second match?

Excel Video Tutorials / Excel Dashboards Reports

6. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Vlookup With 2 Conditions

Because you have duplicates in column A, the concatenated index/match method is best. See the attachment. You can hide the new column E.

7. ## Re: Vlookup With 2 Conditions

If you want a formula in D2 to just find the first row where MATT_INUP!A\$2:A\$11 = A2 and where MATT_INUP!D\$2:D\$11 = 0 and return the corresponding value from MATT_INUP!B\$2:B\$11 then one way, without helper columns, is

=INDEX(MATT_INUP!B\$2:B\$11,MATCH(1,(MATT_INUP!A\$2:A\$11=A2)*(MATT_INUP!D\$2:D\$11=0),0))

confirmed with CTRL+SHIFT+ENTER

edit:....actually that isn't too far from the formula you suggested in your first post, that would work too, except you need to lose the quotes around 0 (because the zeroes aren't text) and the 4 at the end should be a 2 (because you want to return the value from the 2nd column of the range, column B, not the 4th)...so your formula then would be

=INDEX(MATT_INUP!\$A\$2:\$D\$10000,MATCH(1,(MATT_INUP!\$A\$2:\$A\$10000=A2)*(MATT_INUP!\$D\$2:\$D\$10000=0),0),2)
Last edited by daddylonglegs; June 26th, 2007 at 07:50.

Excel Video Tutorials / Excel Dashboards Reports

8. Senior Member
Join Date
29th December 2005
Posts
200

## Re: Vlookup With 2 Conditions

Originally Posted by ByTheCringe2
Because you have duplicates in column A, the concatenated index/match method is best. See the attachment. You can hide the new column E.
this worked but i want to mess with it some more and try the other guys example before posting back in case I have a question...kinda got working on a different report before i can go back to this one...

Excel Video Tutorials / Excel Dashboards Reports

9. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Vlookup With 2 Conditions

Fair enough.

10. Senior Member
Join Date
29th December 2005
Posts
200

## Re: Vlookup With 2 Conditions

If you want a formula in D2 to just find the first row where MATT_INUP!A\$2:A\$11 = A2 and where MATT_INUP!D\$2:D\$11 = 0 and return the corresponding value from MATT_INUP!B\$2:B\$11 then one way, without helper columns, is

=INDEX(MATT_INUP!B\$2:B\$11,MATCH(1,(MATT_INUP!A\$2:A\$11=A2)*(MATT_INUP!D\$2:D\$11=0),0))

confirmed with CTRL+SHIFT+ENTER

edit:....actually that isn't too far from the formula you suggested in your first post, that would work too, except you need to lose the quotes around 0 (because the zeroes aren't text) and the 4 at the end should be a 2 (because you want to return the value from the 2nd column of the range, column B, not the 4th)...so your formula then would be

=INDEX(MATT_INUP!\$A\$2:\$D\$10000,MATCH(1,(MATT_INUP!\$A\$2:\$A\$10000=A2)*(MATT_INUP!\$D\$2:\$D\$10000=0),0),2)
=INDEX(MATT_INUP!\$A\$2:\$D\$10000,MATCH(1,(MATT_INUP!\$A\$2:\$A\$10000=A2)*(MATT_INUP!\$D\$2:\$D\$10000=0),0),2)

yea this one seems to work pretty good. Thank you, ill add this to my notes to use on double column references in the future!

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno