match values in two column and return value in
 

I want to look in two columns. I want to look in the first column for a cell
that matches the data in another cell. And with that value, I want to find a
blank value in the second column. Once it finds the row with this value, I
want to return the value in column G of this row. Example:

Col A Col B Col G
TBD 1 20000
TBD 1 X (This is where I want to look for the
value TBD 1
in column A and blank in Col B, so
it returns
the value 20000 from column G)
TBD 2 10000
TBD 2 X

Can you help with a formula?

Thank You,

PW

match values in two column and return value in
 

Is the item you're looking for always the 1st item of that group? That's how
it looks in your posted sample. If that's the case try this:

=INDEX(G2:G10,MATCH("TBD 1",A2:A10,0))

Another possibility:

=SUMPRODUCT(--(A2:A10="TBD 1"),--(B2:B10=""),G2:G10)


--
Biff
Microsoft Excel MVP


"PW" wrote in message
...
I want to look in two columns. I want to look in the first column for a
cell
that matches the data in another cell. And with that value, I want to
find a
blank value in the second column. Once it finds the row with this value,
I
want to return the value in column G of this row. Example:

Col A Col B Col G
TBD 1 20000
TBD 1 X (This is where I want to look for the
value TBD 1
in column A and blank in Col B, so
it returns
the value 20000 from column G)
TBD 2 10000
TBD 2 X

Can you help with a formula?

Thank You,

PW

match values in two column and return value in
 

It's not always the first item. However, I did get it to work with INDEX and
MATCH.
Thank You!

PW


"T. Valko" wrote:

Is the item you're looking for always the 1st item of that group? That's how
it looks in your posted sample. If that's the case try this:

=INDEX(G2:G10,MATCH("TBD 1",A2:A10,0))

Another possibility:

=SUMPRODUCT(--(A2:A10="TBD 1"),--(B2:B10=""),G2:G10)


--
Biff
Microsoft Excel MVP


"PW" wrote in message
...
I want to look in two columns. I want to look in the first column for a
cell
that matches the data in another cell. And with that value, I want to
find a
blank value in the second column. Once it finds the row with this value,
I
want to return the value in column G of this row. Example:

Col A Col B Col G
TBD 1 20000
TBD 1 X (This is where I want to look for the
value TBD 1
in column A and blank in Col B, so
it returns
the value 20000 from column G)
TBD 2 10000
TBD 2 X

Can you help with a formula?

Thank You,

PW

match values in two column and return value in
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"PW" wrote in message
...
It's not always the first item. However, I did get it to work with INDEX
and
MATCH.
Thank You!

PW


"T. Valko" wrote:

Is the item you're looking for always the 1st item of that group? That's
how
it looks in your posted sample. If that's the case try this:

=INDEX(G2:G10,MATCH("TBD 1",A2:A10,0))

Another possibility:

=SUMPRODUCT(--(A2:A10="TBD 1"),--(B2:B10=""),G2:G10)


--
Biff
Microsoft Excel MVP


"PW" wrote in message
...
I want to look in two columns. I want to look in the first column for a
cell
that matches the data in another cell. And with that value, I want to
find a
blank value in the second column. Once it finds the row with this
value,
I
want to return the value in column G of this row. Example:

Col A Col B Col G
TBD 1 20000
TBD 1 X (This is where I want to look for
the
value TBD 1
in column A and blank in Col B,
so
it returns
the value 20000 from column G)
TBD 2 10000
TBD 2 X

Can you help with a formula?

Thank You,

PW