Matching

[andream02]

I have two lists that I'm trying to compare. I have a list of POs, in a particular order, where I'm trying to match up actuals. I've provided two (2) lists below. The first is the Opened PO's List. The second is the Actual Spend to Date List.

I want to match the actuals, where applicable, in the exact order as the original list (OPENED POs). It's important to note that not every PO has Actual Spend to date. In that case, I want excel to return a zero.

I know I can't use vlookup because there are PO#s listed more than once with different amounts and I'm having trouble with the Index(small) function here. I keep getting a #Num error when I use the Index function. Not sure what the best function would be in the case. Any and all help is much appreciated!

OPENED PO LIST
P.O.# Net price
4300008645 $3,930.00
4300008645 $10,500.00
4400018611 $3,652.63
4400019139 $45,000.00
4400019143 $55,000.00
4400019333 $265,000.00
4400019334 $335,000.00
4400019489 $675.00
4400019512 $7,500.00
4400019513 $22,500.00
4300009755 $324.41
4300009755 $0.31
4300009755 $397.71
4300009756 $129.76
4300009757 $530.00
4300009758 $796.38
4300009758 $0.76
4300009758 $365.00
4300009758 $850.00
4400019925 $20,000.00
4300010029 $4,020.00
4300010056 $181.02
4300010056 $171.98
4300010056 $100.00
4300010460 $706.03
4300010460 $191.97
4300010460 $953.76
4300010463 $122.41
4300010463 $116.30
4300010463 $148.27
4300010467 $641.94
4300010467 $350.00
4300010507 $69.95
4300010507 $66.45
4300010507 $175.00
4300010659 $463.32
4300010659 $275.00
4400021611 $2,905.25
4300011002 $115.00
4300011002 $0.11
4300011002 $175.00
4300011751 $1.27
4300011751 $1,995.94
4300011751 $150.00
4300011751 $175.00
4300011752 $95.90
4300011752 $200.00
4300011752 $175.00
4300011753 $2,085.62
4300011753 $1.98
4300011753 $591.00
4300011754 $151.12
4300011754 $175.00


ACTUAL SPEND TO DATE
Purchasing Document Value in Obj. Crcy
4300009757 11,342.00
4300008645 84,102.00
4400019489 675.00
4400019139 22,793.51
4400019333 1,322.12
4400019333 56,477.47
4400019333 73.14
4400019333 726.77
4400019333 34.89
4400019333 33.27
4400019139 11,311.77
4400019333 58.79
4400019512 1,294.26
4400019333 47,533.44
4400019333 8.11
4400019139 7,282.52
4400019512 1,382.87
4400019333 7.37
4400019333 45,603.20
4400021611 1,452.63
4400019925 5,890.00
4300010056 3,620.42
4300010056 68.79
4300009755 324.41
4300009755 15.50
4300009758 15,927.60
4300009758 216.60
4300009758 365.00
4300009756 648.80
4300009756 24.00
4300010507 2,619.63
4300010507 71.10
4300010467 22,467.90
4300010659 9,266.40
4300011002 2,300.00
4300011002 52.25
4300010460 45,327.12
4300010460 1,533.43
4300008645 11,235.00
4300009755 397.71
4300009758 850.00
4300010507 187.25
4300010467 350.00
4300010659 275.00
4300011002 175.00
4300010460 916.00

 

[gecs]

Presuming your Opened PO's List starts in A1, headers on row 1 and the Actual Spend to Date List starts in F1, same sheet, headers on row 1, in C2 try:

=IFERROR(LOOKUP(2,1/(($F$2:$F$47=A2)*($G$2:$G$47=B2)),ROW($F$2:$F$47)-1),0)

copied down.

The formula will work in 2007 and 2010 and will return the record position in the second list in case of a match and 0 if that record in the first list has no match in the second list.

For a prior to 2007 version please use:

=IF(ISNA(LOOKUP(2,1/(($F$2:$F$47=A2)*($G$2:$G$47=B2)),ROW($F$2:$F$47)-1)),0,LOOKUP(2,1/(($F$2:$F$47=A2)*($G$2:$G$47=B2)),ROW($F$2:$F$47)-1))

 

[andream02]

Thanks, I tried this and while I see the value of the row where the match resides, I would prefer to have the actual value returned. How can I tweak the formula you provided you do that?

 

[gecs]

Change the third argument of the LOOKUP function (ROW($F$2:$F$47)-1) to $G$2:$G$47.