vlookup Match???

[Parra]

I need to do a vlookup but only if the totals match.

Sheet 1
C2 = Customer Number
O2 = Dollar amount

Sheet 2
A2 = Customer Number
K2 = Dollar amount
I2 = "Data I need"

So what i need is to lookup c2 in sheet 2 and if o2 in sheet 1 matches k2 in sheet 2 I want the value in sheet 2 I2 returned. Any suggestions?

Thanks

[Yogi Anand]

use the following formula in a cell in Sheet1

=IF(AND(C2=Sheet2!A2,O2=Sheet2!K2),Sheet2!I2,"no match")

[Asala42]

Hey,

I think I follow you...

I need to do a vlookup but only if the totals match.

Sheet 1
C2 = Customer Number
O2 = Dollar amount

Sheet 2
A2 = Customer Number
K2 = Dollar amount
I2 = "Data I need"

So what i need is to lookup c2 in sheet 2 and if o2 in sheet 1 matches k2 in sheet 2 I want the value in sheet 2 I2 returned. Any suggestions?

=IF(O2=VLOOKUP(A2,Sheet2Range,11,0),VLOOKUP(A2,Sheet2Range,9,0),"")

Is that about right?
Adam

[Parra]

OK, I looked at what both of you sent me, I need a vlookup and match formula because. The customer appears more than once and the total changes. That is why I could not use it, or am I getting it wrong?

I want to match both the customer number and the customer total and get a description back.

Thanks

Let me know I got it wrong.

[Al Chara]

If you want to do it the easy way then you one of the other posts, or you can do it the hard way:

=IF(INDIRECT(ADDRESS(MATCH(VLOOKUP(Sheet1!C2,Sheet2!A:K,9,Sheet2!A:A),Sheet2!I:I,0),15,,,"Sheet1"))=INDIRECT(ADDRESS(MATCH(VLOOKUP(Sheet1!C2,Sheet2!A:K,9,Sheet2!A:A),Sheet2!I:I,0),11,,,"Sheet2")),VLOOKUP(Sheet1!C2,Sheet2!A:K,9,Sheet2!A:A),"")

[Asala42]

Hey again,

Some further clarifications are needed (I think).

Do customer numbers repeat on both Sheet1 and Sheet 2? Do you have an actual totals line on these sheets or do we need to apply a sumif and/or sumproduct?

Taking a wild stab in the dark here, but how about:

=IF(O2=SUMPRODUCT((Sheet2!$A$2:$A$100=Sheet1!A2)*(Sheet2!$K$2:$K$100)),VLOOKUP(A2,Sheet2!$A$2:$K$100,9,0),"")

Adam

[Parra]

OK, I'm up for the challenge I'll try it the hard way. I'll try to figure out what you wrote. Thanks Al

[Parra]

OK, I'm up for the challenge I'll try it the hard way. I'll try to figure out what you wrote. Thanks Al

[Parra]

The customers repeat on both sheets.

[Al Chara]

You know that for VLOOKUP to work the list has to be sorted in ascending order.