Sumproduct ... Maybe?
 

Excel2003 ... Need Formula in Col D of TabSheet1 that will compare values in
Cols A, B, C of TabSheet1 with values of Cols A, B, C in Tabsheet2 & then
return value found in Col G of Tabsheet2 to Col D of Tabsheet1.

Note:

Cols A,B,C = Text (both TabSheets)
TabSheet2 Col G is a DATE (mm/dd/yy) which I wish to get populated into
TabSheet1 Col D when all 3 criteria match.

So far my attempts with SUMPRODUCT are returning the #Value error.

Thanks ... Kha

Sumproduct ... Maybe?
 

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

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

Ken wrote:

Excel2003 ... Need Formula in Col D of TabSheet1 that will compare values in
Cols A, B, C of TabSheet1 with values of Cols A, B, C in Tabsheet2 & then
return value found in Col G of Tabsheet2 to Col D of Tabsheet1.

Note:

Cols A,B,C = Text (both TabSheets)
TabSheet2 Col G is a DATE (mm/dd/yy) which I wish to get populated into
TabSheet1 Col D when all 3 criteria match.

So far my attempts with SUMPRODUCT are returning the #Value error.

Thanks ... Kha

--

Dave Peterson

Sumproduct ... Maybe?
 

Dave ... Good morning

I edited Cols & Ranges & Formula works fine ... Formula did return a
default date when the INDEX Range contained an empty cell (meaning "date" not
filled in), but I was able to take care of this.

VLOOKUP is awesome for single Criteria Lookups & picking the value.
SUMPRODUCT is awesome for multiple Criteria lookups & summing the value.

However, I am contstantly data mining List where I wish a data field in one
List to be in a Col in the other based on "Multiple Col Match Criteria" ...
In other words ... Match multiple criteria (like SUMPRODUCT) & then pick a
value (like VLOOKUP).

The Formula you provided here accomplishes this for me ... I do not know who
originally created this Formula & hit this homerun ... but today it is you
.... :)

Thanks ... Kha




"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

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

Ken wrote:

Excel2003 ... Need Formula in Col D of TabSheet1 that will compare values in
Cols A, B, C of TabSheet1 with values of Cols A, B, C in Tabsheet2 & then
return value found in Col G of Tabsheet2 to Col D of Tabsheet1.

Note:

Cols A,B,C = Text (both TabSheets)
TabSheet2 Col G is a DATE (mm/dd/yy) which I wish to get populated into
TabSheet1 Col D when all 3 criteria match.

So far my attempts with SUMPRODUCT are returning the #Value error.

Thanks ... Kha

--

Dave Peterson

Sumproduct ... Maybe?
 

Dave ... (Hi)

This Formula appeared to be working well ... Then results went "funky" ... I
have another person working this file & I am thinking a "sort" probably
occurred???

1: Do our comparison List have to be sorted in any particular order for
this Formula to work?

2: If we sort after Formula is in place will this compromise the value
returned?

Note: We are keeping all data (both List) intact as we sort ... it is just
our sort order that is changing ...

Thanks ... Kha

"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

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

Ken wrote:

Excel2003 ... Need Formula in Col D of TabSheet1 that will compare values in
Cols A, B, C of TabSheet1 with values of Cols A, B, C in Tabsheet2 & then
return value found in Col G of Tabsheet2 to Col D of Tabsheet1.

Note:

Cols A,B,C = Text (both TabSheets)
TabSheet2 Col G is a DATE (mm/dd/yy) which I wish to get populated into
TabSheet1 Col D when all 3 criteria match.

So far my attempts with SUMPRODUCT are returning the #Value error.

Thanks ... Kha

--

Dave Peterson

Sumproduct ... Maybe?
 

The data doesn't have to be sorted--but it does only return the data associated
with the first matching requirements.

I'd check the formula for a couple of things.

Make sure that it was array entered (sometimes forgotten if you change the the
formula)

Make sure that the ranges include all the rows you need.

Ken wrote:

Dave ... (Hi)

This Formula appeared to be working well ... Then results went "funky" ... I
have another person working this file & I am thinking a "sort" probably
occurred???

1: Do our comparison List have to be sorted in any particular order for
this Formula to work?

2: If we sort after Formula is in place will this compromise the value
returned?

Note: We are keeping all data (both List) intact as we sort ... it is just
our sort order that is changing ...

Thanks ... Kha

"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

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

Ken wrote:

Excel2003 ... Need Formula in Col D of TabSheet1 that will compare values in
Cols A, B, C of TabSheet1 with values of Cols A, B, C in Tabsheet2 & then
return value found in Col G of Tabsheet2 to Col D of Tabsheet1.

Note:

Cols A,B,C = Text (both TabSheets)
TabSheet2 Col G is a DATE (mm/dd/yy) which I wish to get populated into
TabSheet1 Col D when all 3 criteria match.

So far my attempts with SUMPRODUCT are returning the #Value error.

Thanks ... Kha

--

Dave Peterson


--

Dave Peterson

Sumproduct ... Maybe?
 

Dave ...

I was actually hoping for this type of answer ... That said, I am not
certain what happened here ... So, I sorted both list ... Then I re-inserted
the Formula ... Captured the Date value I needed ... & then replaced the
Formula with a Value before we started further manipulating the 2 List ...
So, I think we are off the hook ... :)

As far as returning the results from the 1st entry found this should not be
an issue ... Individually, the 3 Criteria Cols have repeat values ...
However, collectively they should not ... This is why desire to lookup on
multiple criteria.

Fingers now crossed ... Thanks for the guidance ... Kha

"Dave Peterson" wrote:

The data doesn't have to be sorted--but it does only return the data associated
with the first matching requirements.

I'd check the formula for a couple of things.

Make sure that it was array entered (sometimes forgotten if you change the the
formula)

Make sure that the ranges include all the rows you need.

Ken wrote:

Dave ... (Hi)

This Formula appeared to be working well ... Then results went "funky" ... I
have another person working this file & I am thinking a "sort" probably
occurred???

1: Do our comparison List have to be sorted in any particular order for
this Formula to work?

2: If we sort after Formula is in place will this compromise the value
returned?

Note: We are keeping all data (both List) intact as we sort ... it is just
our sort order that is changing ...

Thanks ... Kha

"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

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

Ken wrote:

Excel2003 ... Need Formula in Col D of TabSheet1 that will compare values in
Cols A, B, C of TabSheet1 with values of Cols A, B, C in Tabsheet2 & then
return value found in Col G of Tabsheet2 to Col D of Tabsheet1.

Note:

Cols A,B,C = Text (both TabSheets)
TabSheet2 Col G is a DATE (mm/dd/yy) which I wish to get populated into
TabSheet1 Col D when all 3 criteria match.

So far my attempts with SUMPRODUCT are returning the #Value error.

Thanks ... Kha

--

Dave Peterson


--

Dave Peterson