match values in two column and return value in

[PW]

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

 

[T. Valko]

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)

 

[PW]

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

PW

 

[T. Valko]

You're welcome. Thanks for the feedback!