Is there a way to do a vlookup based on a valid match of 2 values on seperate
columns?

Example Data.....
3/12/07 3110 23.23
3/13/07 3110 555.21
3/14/07 3110 46.01

I want to lookup based on a match criteria of 3/13/07 and 3110 and return
the value of 555.21

I know that I could concatenate the 2 columns and do a lookup of the
concatenated column, but I would like to keep the data as it was originally
submitted.

I know it would work that way, but I would like to learn if it could be done
differently without that step.

Thank you in advance for any and all input.

Well, maybe this? =IF(AND(A2=3/13/07,B2=3110),C2,"")



--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"stickandrock" wrote:

Is there a way to do a vlookup based on a valid match of 2 values on seperate
columns?

Example Data.....
3/12/07 3110 23.23
3/13/07 3110 555.21
3/14/07 3110 46.01

I want to lookup based on a match criteria of 3/13/07 and 3110 and return
the value of 555.21

I know that I could concatenate the 2 columns and do a lookup of the
concatenated column, but I would like to keep the data as it was originally
submitted.

I know it would work that way, but I would like to learn if it could be done
differently without that step.

Thank you in advance for any and all input.

=SUMPRODUCT(--(A1:A100=--"3/13/07"),--(B1:B100=3110),C1:C100)

or

=INDEX(C1:C100,MATCH(1,(A1:A100=--"3/13/07")*(B1:B100=3110),0))

The second formula required ctrl+shift+enter, not just enter


"stickandrock" wrote:

Is there a way to do a vlookup based on a valid match of 2 values on seperate
columns?

Example Data.....
3/12/07 3110 23.23
3/13/07 3110 555.21
3/14/07 3110 46.01

I want to lookup based on a match criteria of 3/13/07 and 3110 and return
the value of 555.21

I know that I could concatenate the 2 columns and do a lookup of the
concatenated column, but I would like to keep the data as it was originally
submitted.

I know it would work that way, but I would like to learn if it could be done
differently without that step.

Thank you in advance for any and all input.

It stills needs to be some form of a lookup though. The resulting matching
row is not known. My sample criterea is going to be dynamic.

"Dave F" wrote:

Well, maybe this? =IF(AND(A2=3/13/07,B2=3110),C2,"")



--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"stickandrock" wrote:

Is there a way to do a vlookup based on a valid match of 2 values on seperate
columns?

Example Data.....
3/12/07 3110 23.23
3/13/07 3110 555.21
3/14/07 3110 46.01

I want to lookup based on a match criteria of 3/13/07 and 3110 and return
the value of 555.21

I know that I could concatenate the 2 columns and do a lookup of the
concatenated column, but I would like to keep the data as it was originally
submitted.

I know it would work that way, but I would like to learn if it could be done
differently without that step.

Thank you in advance for any and all input.

Sorry, didn't understand that the lookup was a requirement. See teethless
mama's response.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"stickandrock" wrote:

It stills needs to be some form of a lookup though. The resulting matching
row is not known. My sample criterea is going to be dynamic.

"Dave F" wrote:

Well, maybe this? =IF(AND(A2=3/13/07,B2=3110),C2,"")



--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"stickandrock" wrote:

Is there a way to do a vlookup based on a valid match of 2 values on seperate
columns?

Example Data.....
3/12/07 3110 23.23
3/13/07 3110 555.21
3/14/07 3110 46.01

I want to lookup based on a match criteria of 3/13/07 and 3110 and return
the value of 555.21

I know that I could concatenate the 2 columns and do a lookup of the
concatenated column, but I would like to keep the data as it was originally
submitted.

I know it would work that way, but I would like to learn if it could be done
differently without that step.

Thank you in advance for any and all input.

Not sure why I would do a multiplication operation when I am trying to
retrieve the value of a different column in that same row.

I will try it though....

It does work, Thanks!

Just seems to be a little misleading to read.


"Teethless mama" wrote:

=SUMPRODUCT(--(A1:A100=--"3/13/07"),--(B1:B100=3110),C1:C100)

or

=INDEX(C1:C100,MATCH(1,(A1:A100=--"3/13/07")*(B1:B100=3110),0))

The second formula required ctrl+shift+enter, not just enter


"stickandrock" wrote:

Is there a way to do a vlookup based on a valid match of 2 values on seperate
columns?

Example Data.....
3/12/07 3110 23.23
3/13/07 3110 555.21
3/14/07 3110 46.01

I want to lookup based on a match criteria of 3/13/07 and 3110 and return
the value of 555.21

I know that I could concatenate the 2 columns and do a lookup of the
concatenated column, but I would like to keep the data as it was originally
submitted.

I know it would work that way, but I would like to learn if it could be done
differently without that step.

Thank you in advance for any and all input.

See here for an explanation of the -- and how multiplication is factored into
the logic: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Note the page discusses -- and multiplication in the context of the
SUMPRODUCT function, but the same logic holds for the formula
=INDEX(C1:C100,MATCH(1,(A1:A100=--"3/13/07")*(B1:B100=3110),0))

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"stickandrock" wrote:

Not sure why I would do a multiplication operation when I am trying to
retrieve the value of a different column in that same row.

I will try it though....

It does work, Thanks!

Just seems to be a little misleading to read.


"Teethless mama" wrote:

=SUMPRODUCT(--(A1:A100=--"3/13/07"),--(B1:B100=3110),C1:C100)

or

=INDEX(C1:C100,MATCH(1,(A1:A100=--"3/13/07")*(B1:B100=3110),0))

The second formula required ctrl+shift+enter, not just enter


"stickandrock" wrote:

Is there a way to do a vlookup based on a valid match of 2 values on seperate
columns?

Example Data.....
3/12/07 3110 23.23
3/13/07 3110 555.21
3/14/07 3110 46.01

I want to lookup based on a match criteria of 3/13/07 and 3110 and return
the value of 555.21

I know that I could concatenate the 2 columns and do a lookup of the
concatenated column, but I would like to keep the data as it was originally
submitted.

I know it would work that way, but I would like to learn if it could be done
differently without that step.

Thank you in advance for any and all input.

For the multiply symbol read AND, i.e.:

if (A1:A100=--"3/13/07") AND (B1:B100=3110) then use this in the match
function as you cycle through each cell in the range.

Hope this helps.

Pete

On Mar 16, 2:54 pm, stickandrock
wrote:
Not sure why I would do a multiplication operation when I am trying to
retrieve the value of a different column in that same row.

I will try it though....

It does work, Thanks!

Just seems to be a little misleading to read.



"Teethless mama" wrote:
=SUMPRODUCT(--(A1:A100=--"3/13/07"),--(B1:B100=3110),C1:C100)

or

=INDEX(C1:C100,MATCH(1,(A1:A100=--"3/13/07")*(B1:B100=3110),0))

The second formula required ctrl+shift+enter, not just enter

"stickandrock" wrote:

Is there a way to do a vlookup based on a valid match of 2 values on seperate
columns?

Example Data.....
3/12/07 3110 23.23
3/13/07 3110 555.21
3/14/07 3110 46.01

I want to lookup based on a match criteria of 3/13/07 and 3110 and return
the value of 555.21

I know that I could concatenate the 2 columns and do a lookup of the
concatenated column, but I would like to keep the data as it was originally
submitted.

I know it would work that way, but I would like to learn if it could be done
differently without that step.

Thank you in advance for any and all input.- Hide quoted text -

- Show quoted text -

Learn something new every day....

Thanks for insight and quick responses.

"Pete_UK" wrote:

For the multiply symbol read AND, i.e.:

if (A1:A100=--"3/13/07") AND (B1:B100=3110) then use this in the match
function as you cycle through each cell in the range.

Hope this helps.

Pete

On Mar 16, 2:54 pm, stickandrock
wrote:
Not sure why I would do a multiplication operation when I am trying to
retrieve the value of a different column in that same row.

I will try it though....

It does work, Thanks!

Just seems to be a little misleading to read.



"Teethless mama" wrote:
=SUMPRODUCT(--(A1:A100=--"3/13/07"),--(B1:B100=3110),C1:C100)

or

=INDEX(C1:C100,MATCH(1,(A1:A100=--"3/13/07")*(B1:B100=3110),0))

The second formula required ctrl+shift+enter, not just enter

"stickandrock" wrote:

Is there a way to do a vlookup based on a valid match of 2 values on seperate
columns?

Example Data.....
3/12/07 3110 23.23
3/13/07 3110 555.21
3/14/07 3110 46.01

I want to lookup based on a match criteria of 3/13/07 and 3110 and return
the value of 555.21

I know that I could concatenate the 2 columns and do a lookup of the
concatenated column, but I would like to keep the data as it was originally
submitted.

I know it would work that way, but I would like to learn if it could be done
differently without that step.

Thank you in advance for any and all input.- Hide quoted text -

- Show quoted text -