Vlookup is only pulling the first occurance. INDEX? How?!

[davejackson]

Problem I am facing: Vlookup returns a value based only on the first occurrence of the lookup_value.

Based on the below example, when entering the vlookup formula into column C of Sheet2, using the corresponding column B value as the lookup_value, all three pull $2,352 - the first occurrence of Acct # 1401 in Sheet1.

Sheet1 ~ my reference table
A B C
Investment ID Acct # $ Value
00001 1401 2,352
00001 1500 5,856
00001 1600 2,147
00002 1401 1,506
00002 1621 525
00002 1701 100
00003 1401 900
00003 1421 8,568
00003 1450 9,656

Sheet2 ~ I'm writing the vlookup formula in column C
A B C
Investment ID Acct # $ Value
00001 1401
00002 1401
00003 1401

How can I write a formula to pull the $ value based on the Acct # (col B) AND investment ID (col A)?

Please note that I have no prior experience with the INDEX function, so I would greatly appreciate it if you keep that in mind when providing me with a response

Thanks!
Dave

 

[barry houdini]

Hello Dave, welcome to MrExcel

One way would be to use LOOKUP like this in sheet2 C2 copied down

=LOOKUP(2,1/((Sheet1!A$2:A$100=A2)*(Sheet1!B$2:B$100=B2)),Sheet1!C$2:C$100)

 

[kwilliam10]

You could write an array:

I would name the a2:a10, b2:b10, and c2:c10 ranges, on sheet1.....(invest, acct, amount)...respectively.

Then enter this formula on sheet2!c2:


=sum((invest=sheet2!a2)*(acct=sheet2!b2)*(amount))


hit ctr-shift-enter to enter the formula.

Regards,

Keith W.

 

[davejackson]

You could write an array:

I would name the a2:a10, b2:b10, and c2:c10 ranges, on sheet1.....(invest, acct, amount)...respectively.

Then enter this formula on sheet2!c2:


=sum((invest=sheet2!a2)*(acct=sheet2!b2)*(amount))


hit ctr-shift-enter to enter the formula.

Regards,

Keith W.

Hello Dave, welcome to MrExcel

One way would be to use LOOKUP like this in sheet2 C2 copied down

=LOOKUP(2,1/((Sheet1!A$2:A$100=A2)*(Sheet1!B$2:B$100=B2)),Sheet1!C$2:C$100)

Thank you so much guys, both of these worked.

If I could bug you one more time and have you briefly explain HOW both of these functions work (or the theory behind them), I would greatly appreciate it... I am hoping to learn so that I can reproduce either of these in the future, not just a quick fix this one time.

Thanks again!
Dave

 

[kwilliam10]

Dave,

Basically the array option I gave you, is a multi-criteria sumif. You can have more criteri than what I gave you. I always find it easier to name the various ranges: You would name each range of cells (columner) for each criteria. Lastly, name the range of cells that has the resulting data you want returned.

=sum((criteria1=a2)*(criteria2=b2)*(criteria3=c2)*(criteria4=c3)*(result))

criteria1, criteria2, etc (as far as I know...you can go on and on with these crieteria)..would be named ranges for your columns of data. result would be the named range for what you want to sum up. Two things to note.....

1) the named ranges have to be the exact same number of rows....you are establishing links between the various columns...so you can't confuse it by having different lengths of rows; and

2) you can substitute average, max, min, count for sum.

Because this is an array...you have to ctrl-shift-enter the formula. As an alternative, you can replace the "sum" with "sumproduct". The rest of the formula should be the same. I'm just begining to use this, instead of the array.....I'm not totally sure which is more efficient...or which is best for whatever scenario.

Hope this helps,

Keith W.

 

[davejackson]

Dave,

Basically the array option I gave you, is a multi-criteria sumif. You can have more criteri than what I gave you. I always find it easier to name the various ranges: You would name each range of cells (columner) for each criteria. Lastly, name the range of cells that has the resulting data you want returned.

=sum((criteria1=a2)*(criteria2=b2)*(criteria3=c2)*(criteria4=c3)*(result))

criteria1, criteria2, etc (as far as I know...you can go on and on with these crieteria)..would be named ranges for your columns of data. result would be the named range for what you want to sum up. Two things to note.....

1) the named ranges have to be the exact same number of rows....you are establishing links between the various columns...so you can't confuse it by having different lengths of rows; and

2) you can substitute average, max, min, count for sum.

Because this is an array...you have to ctrl-shift-enter the formula. As an alternative, you can replace the "sum" with "sumproduct". The rest of the formula should be the same. I'm just begining to use this, instead of the array.....I'm not totally sure which is more efficient...or which is best for whatever scenario.

Hope this helps,

Keith W.

How do you name a range of cells?

 

[kwilliam10]

Look Here:

http://www.contextures.com/xlNames01.html

HTH,

Keith W.