Vlookup With Multiple Instances in Data Table

[whasupwityou]

Hello all,

First I have to say that I've not posted a question in over 5 years
due to the fantastic results I've had searching for solutions in this
group.

I'm developing a Pivot Table to calculate PO balances. The problem
I've having is that a PO number resides in every row in the database.
I'm looking for a solution that will pull the PO amount for the first
instance into a new column and then pull zeros for the remaining
instances. Any assistance with this project is gratefully
appreciated. Thank you in advance, Ron

 

[T. Valko]

You can try something like this:

=IF(COUNTIF(A$1:A1,A1)=1,INDEX(Balance,MATCH(A1,PO,0),"")

Copy down as needed.

 

[T. Valko]

Ooops! Missing a closing ")" :

=IF(COUNTIF(A$1:A1,A1)=1,INDEX(Balance,MATCH(A1,PO,0)),"")

 

[whasupwityou]

Ooops! Missing a closing ")" :

=IF(COUNTIF(A$1:A1,A1)=1,INDEX(Balance,MATCH(A1,PO,0)),"")

--
Biff
Microsoft Excel MVP






- Show quoted text -

Hi Biff, thank you for the quick response. I'm not getting anything
back, I'm getting blank. Should this formula be an Array? I'm not
sure I explained the situation with the data table correctly. The
data table has multiple PO numbers with multiple invoices. Some PO's
will have one invoice while others will have 2 or more and as many as
15.
Thank you for your assistance.

 

[T. Valko]

You'll need to describe (in great detail) or post a sample showing how your
data is setup.

--
Biff
Microsoft Excel MVP

Ooops! Missing a closing ")" :

=IF(COUNTIF(A$1:A1,A1)=1,INDEX(Balance,MATCH(A1,PO,0)),"")

--
Biff
Microsoft Excel MVP






- Show quoted text -

Hi Biff, thank you for the quick response. I'm not getting anything
back, I'm getting blank. Should this formula be an Array? I'm not
sure I explained the situation with the data table correctly. The
data table has multiple PO numbers with multiple invoices. Some PO's
will have one invoice while others will have 2 or more and as many as
15.
Thank you for your assistance.

 

[Charles Williams]

One simple and fast solution would be to sort the data PO ascending, Invoice
amount descending, and then add a helper column:
assumes that PO number is in A and invoice amount in B and that data starts
in row 2
=If(a2<>a1,b2,0)
and fill down

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

Ooops! Missing a closing ")" :

=IF(COUNTIF(A$1:A1,A1)=1,INDEX(Balance,MATCH(A1,PO,0)),"")

--
Biff
Microsoft Excel MVP






- Show quoted text -

Hi Biff, thank you for the quick response. I'm not getting anything
back, I'm getting blank. Should this formula be an Array? I'm not
sure I explained the situation with the data table correctly. The
data table has multiple PO numbers with multiple invoices. Some PO's
will have one invoice while others will have 2 or more and as many as
15.
Thank you for your assistance.