Vlookup

[shaunap]

Hi All,

I have a Vlookup formula that works fine. I would like to tweak it so that
it will look up the value based on conditions. I'm not sure if this can be
done or not so any help will be appreciated.

My data is 4 columns. I am searching column A and returning column C, but
would like for the formula to look at column D and if it sees a particular
value to continue to search the rest of the data for another exact match.

I hope this makes sense to somebody. Thank you in advance for your help.

Shauna

[Dave Peterson]

Saved from a few previous posts:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

shaunap wrote:
>
> Hi All,
>
> I have a Vlookup formula that works fine. I would like to tweak it so that
> it will look up the value based on conditions. I'm not sure if this can be
> done or not so any help will be appreciated.
>
> My data is 4 columns. I am searching column A and returning column C, but
> would like for the formula to look at column D and if it sees a particular
> value to continue to search the rest of the data for another exact match.
>
> I hope this makes sense to somebody. Thank you in advance for your help.
>
> Shauna

--

Dave Peterson

[shaunap]

Hi Dave,

Thanks for your reply. I'm not familiar with INDEX functions so I'm a lot
lost here.
I don't understand what this formula does or what info it's grabbing at here.

If I have my data on Sheet 1 arranged as:
A B C D
1 001 xyz 140.00 -
2 004 abc 89.50 -
3 005 lmn 77.76 void
4 005 pmg 98.04 -
5 010 ghi 900.01 -

and on sheet 2 I have a listing of:

A B
1 001
2 002
3 003
4 004
5 005
6 006
etc....

I have in column B of Sheet 2 the following formula:
=IF(ISERROR(VLOOKUP(a1,'sheet1'!a:c,3,FALSE)),"",VLOOKUP(a1,'sheet1'!a:c,3,FALSE))

this gives me a listing of all cheques in numerical order leaving blanks for
ones not listed in sheet 1. Now if a cheque has been voided in sheet 1 and
re-written with the same cheque number a "void" appears in column D and the
correct cheque is listed beneath. I would like for my formula to "skip" the
value in the voided cheque and go to the one that has the correct value.

I tried to follow the logic in the INDEX & MATCH formula you provided but
I'm lost on it. If that does what I'm trying to accomplish here could you
possibly explain the logic to me?

Thank you,
Shauna



"Dave Peterson" wrote:

> Saved from a few previous posts:
>
> =index(othersheet!$c$1:$c$100,
> match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
>
> (all in one cell)
>
> This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
> correctly, excel will wrap curly brackets {} around your formula. (don't type
> them yourself.)
>
> Adjust the range to match--but you can't use the whole column.
>
> This returns the value in othersheet column C when column A and B (of
> othersheet) match A2 and B2 of the sheet with the formula.
>
> And you can add more conditions by just adding more stuff to that product
> portion of the formula:
>
> =index(othersheet!$d$1:$d$100,
> match(1,(a2=othersheet!$a$1:$a$100)
> *(b2=othersheet!$b$1:$b$100)
> *(c2=othersheet!$c$1:$c$100),0))
>
> shaunap wrote:
> >
> > Hi All,
> >
> > I have a Vlookup formula that works fine. I would like to tweak it so that
> > it will look up the value based on conditions. I'm not sure if this can be
> > done or not so any help will be appreciated.
> >
> > My data is 4 columns. I am searching column A and returning column C, but
> > would like for the formula to look at column D and if it sees a particular
> > value to continue to search the rest of the data for another exact match.
> >
> > I hope this makes sense to somebody. Thank you in advance for your help.
> >
> > Shauna
>
> --
>
> Dave Peterson
>

[Dave Peterson]

I thought that you wanted to match up on several fields to return another field.

Instead of =vlookup(a1,sheet2!a:e,5,false)
where you're matching on that single value in A1, I thought you wanted to return
the value when column A matched something, column B matched something else, and
column C matched something else.

This doesn't match your follow up requirements.

It looks as though you could just just sum all the checks that are numbered
#005, but avoid the ones marked Void.

=sumproduct(--(sheet2!a1:a100=a2),--(sheet2!d1:d100<>"void"),(c1:c100))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html





shaunap wrote:
>
> Hi Dave,
>
> Thanks for your reply. I'm not familiar with INDEX functions so I'm a lot
> lost here.
> I don't understand what this formula does or what info it's grabbing at here.
>
> If I have my data on Sheet 1 arranged as:
> A B C D
> 1 001 xyz 140.00 -
> 2 004 abc 89.50 -
> 3 005 lmn 77.76 void
> 4 005 pmg 98.04 -
> 5 010 ghi 900.01 -
>
> and on sheet 2 I have a listing of:
>
> A B
> 1 001
> 2 002
> 3 003
> 4 004
> 5 005
> 6 006
> etc....
>
> I have in column B of Sheet 2 the following formula:
> =IF(ISERROR(VLOOKUP(a1,'sheet1'!a:c,3,FALSE)),"",VLOOKUP(a1,'sheet1'!a:c,3,FALSE))
>
> this gives me a listing of all cheques in numerical order leaving blanks for
> ones not listed in sheet 1. Now if a cheque has been voided in sheet 1 and
> re-written with the same cheque number a "void" appears in column D and the
> correct cheque is listed beneath. I would like for my formula to "skip" the
> value in the voided cheque and go to the one that has the correct value.
>
> I tried to follow the logic in the INDEX & MATCH formula you provided but
> I'm lost on it. If that does what I'm trying to accomplish here could you
> possibly explain the logic to me?
>
> Thank you,
> Shauna
>
> "Dave Peterson" wrote:
>
> > Saved from a few previous posts:
> >
> > =index(othersheet!$c$1:$c$100,
> > match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
> >
> > (all in one cell)
> >
> > This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
> > correctly, excel will wrap curly brackets {} around your formula. (don't type
> > them yourself.)
> >
> > Adjust the range to match--but you can't use the whole column.
> >
> > This returns the value in othersheet column C when column A and B (of
> > othersheet) match A2 and B2 of the sheet with the formula.
> >
> > And you can add more conditions by just adding more stuff to that product
> > portion of the formula:
> >
> > =index(othersheet!$d$1:$d$100,
> > match(1,(a2=othersheet!$a$1:$a$100)
> > *(b2=othersheet!$b$1:$b$100)
> > *(c2=othersheet!$c$1:$c$100),0))
> >
> > shaunap wrote:
> > >
> > > Hi All,
> > >
> > > I have a Vlookup formula that works fine. I would like to tweak it so that
> > > it will look up the value based on conditions. I'm not sure if this can be
> > > done or not so any help will be appreciated.
> > >
> > > My data is 4 columns. I am searching column A and returning column C, but
> > > would like for the formula to look at column D and if it sees a particular
> > > value to continue to search the rest of the data for another exact match.
> > >
> > > I hope this makes sense to somebody. Thank you in advance for your help.
> > >
> > > Shauna
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[shaunap]

Thank you very much Dave,

The links were great. I've got it worked out with the SUM(IF Function.
I'll have to work on the SUMPRODUCT function more so I understand more of it.


Thanks,
Shauna

"Dave Peterson" wrote:

> I thought that you wanted to match up on several fields to return another field.
>
> Instead of =vlookup(a1,sheet2!a:e,5,false)
> where you're matching on that single value in A1, I thought you wanted to return
> the value when column A matched something, column B matched something else, and
> column C matched something else.
>
> This doesn't match your follow up requirements.
>
> It looks as though you could just just sum all the checks that are numbered
> #005, but avoid the ones marked Void.
>
> =sumproduct(--(sheet2!a1:a100=a2),--(sheet2!d1:d100<>"void"),(c1:c100))
>
> Adjust the ranges to match--but you can't use whole columns.
>
> =sumproduct() likes to work with numbers. The -- stuff changes trues and falses
> to 1's and 0's.
>
> Bob Phillips explains =sumproduct() in much more detail here:
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>
> And J.E. McGimpsey has some notes at:
> http://mcgimpsey.com/excel/formulae/doubleneg.html
>
>
>
>
>
> shaunap wrote:
> >
> > Hi Dave,
> >
> > Thanks for your reply. I'm not familiar with INDEX functions so I'm a lot
> > lost here.
> > I don't understand what this formula does or what info it's grabbing at here.
> >
> > If I have my data on Sheet 1 arranged as:
> > A B C D
> > 1 001 xyz 140.00 -
> > 2 004 abc 89.50 -
> > 3 005 lmn 77.76 void
> > 4 005 pmg 98.04 -
> > 5 010 ghi 900.01 -
> >
> > and on sheet 2 I have a listing of:
> >
> > A B
> > 1 001
> > 2 002
> > 3 003
> > 4 004
> > 5 005
> > 6 006
> > etc....
> >
> > I have in column B of Sheet 2 the following formula:
> > =IF(ISERROR(VLOOKUP(a1,'sheet1'!a:c,3,FALSE)),"",VLOOKUP(a1,'sheet1'!a:c,3,FALSE))
> >
> > this gives me a listing of all cheques in numerical order leaving blanks for
> > ones not listed in sheet 1. Now if a cheque has been voided in sheet 1 and
> > re-written with the same cheque number a "void" appears in column D and the
> > correct cheque is listed beneath. I would like for my formula to "skip" the
> > value in the voided cheque and go to the one that has the correct value.
> >
> > I tried to follow the logic in the INDEX & MATCH formula you provided but
> > I'm lost on it. If that does what I'm trying to accomplish here could you
> > possibly explain the logic to me?
> >
> > Thank you,
> > Shauna
> >
> > "Dave Peterson" wrote:
> >
> > > Saved from a few previous posts:
> > >
> > > =index(othersheet!$c$1:$c$100,
> > > match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
> > >
> > > (all in one cell)
> > >
> > > This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
> > > correctly, excel will wrap curly brackets {} around your formula. (don't type
> > > them yourself.)
> > >
> > > Adjust the range to match--but you can't use the whole column.
> > >
> > > This returns the value in othersheet column C when column A and B (of
> > > othersheet) match A2 and B2 of the sheet with the formula.
> > >
> > > And you can add more conditions by just adding more stuff to that product
> > > portion of the formula:
> > >
> > > =index(othersheet!$d$1:$d$100,
> > > match(1,(a2=othersheet!$a$1:$a$100)
> > > *(b2=othersheet!$b$1:$b$100)
> > > *(c2=othersheet!$c$1:$c$100),0))
> > >
> > > shaunap wrote:
> > > >
> > > > Hi All,
> > > >
> > > > I have a Vlookup formula that works fine. I would like to tweak it so that
> > > > it will look up the value based on conditions. I'm not sure if this can be
> > > > done or not so any help will be appreciated.
> > > >
> > > > My data is 4 columns. I am searching column A and returning column C, but
> > > > would like for the formula to look at column D and if it sees a particular
> > > > value to continue to search the rest of the data for another exact match.
> > > >
> > > > I hope this makes sense to somebody. Thank you in advance for your help.
> > > >
> > > > Shauna
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>