vlookup / indexing help

[nkasper]

Hi all,

I want to pull the value of the account code and value of the item within a transaction. I have the data in one worksheet which includes a transaction ID. If the transaction has more than one item (I have labeled this listing ID), I want to pull each instance of the listing ID. If listing ID is 1, then I can just perform a v-lookup on the Trx ID, but if the listing ID for the Trx ID is more than 1, then vlookup will only find the first instance of the TrxID and repeat the same information for every instance of the Trx ID. Keep in mind, the listing ID always starts at 1 for a transaction, therefore, there are multiple Listing IDs of 1, 2, 3.

Here is what I have so far:

if(listingID = 1, vlookup(trxID, range, acct code, false), XXXX

I have indexed the listing ID that is associated with the TRX ID, but I can't figure out how to capture other data based upon two criteria (Trx ID and Listing ID). Hope this makes sense!

Thanks for your help.

 

[T. Valko]

Hi all,

I want to pull the value of the account code and value of the item within a transaction. I have the data in one worksheet which includes a transaction ID. If the transaction has more than one item (I have labeled this listing ID), I want to pull each instance of the listing ID. If listing ID is 1, then I can just perform a v-lookup on the Trx ID, but if the listing ID for the Trx ID is more than 1, then vlookup will only find the first instance of the TrxID and repeat the same information for every instance of the Trx ID. Keep in mind, the listing ID always starts at 1 for a transaction, therefore, there are multiple Listing IDs of 1, 2, 3.

Here is what I have so far:

if(listingID = 1, vlookup(trxID, range, acct code, false), XXXX

I have indexed the listing ID that is associated with the TRX ID, but I can't figure out how to capture other data based upon two criteria (Trx ID and Listing ID). Hope this makes sense!

Thanks for your help.

If you can post a few rows of sample data we may get a better idea of what you're trying to do.

 

[nkasper]

Sorry, for not posting this earlier...I had to figure out how to get the HTML to work (BTW, very easy to use).

Here's the raw data that I am trying to pull from:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">TrxID</td><td style=";">ListingID</td><td style=";">TrxDate</td><td style=";">AccCodeID</td><td style=";">GLCode</td><td style=";">GLFac</td><td style=";">Amount</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">7757571</td><td style="text-align: right;;">1</td><td style="text-align: right;;">5/25/2011</td><td style="text-align: right;;">58</td><td style="text-align: right;;">325</td><td style="text-align: right;;">$440.00 </td><td style="text-align: right;;">$3.36 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">7757571</td><td style="text-align: right;;">2</td><td style="text-align: right;;">5/25/2011</td><td style="text-align: right;;">589</td><td style="text-align: right;;">325</td><td style="text-align: right;;">$440.00 </td><td style="text-align: right;;">$14.00 </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">7757571</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5/25/2011</td><td style="text-align: right;;">411</td><td style="text-align: right;;">325</td><td style="text-align: right;;">$440.00 </td><td style="text-align: right;;">$0.84 </td></tr></tbody></table><p style="width:8.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Account Report</p><br /><br />

Here is where I am trying to pull it to:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>EV</th><th>EW</th><th>EX</th><th>EY</th><th>EZ</th><th>FA</th><th>FB</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;border-bottom: 1px solid black;;">GL ID</td><td style="font-weight: bold;border-bottom: 1px solid black;;">ACC ID</td><td style="font-weight: bold;border-bottom: 1px solid black;;">TrxID</td><td style="font-weight: bold;border-bottom: 1px solid black;;">Amount</td><td style="font-weight: bold;border-bottom: 1px solid black;;">TrxDate</td><td style="font-weight: bold;border-bottom: 1px solid black;;">Description</td><td style="font-weight: bold;border-bottom: 1px solid black;;">Listing ID</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;;">325</td><td style="text-align: right;border-top: 1px solid black;;">58</td><td style="text-align: right;border-top: 1px solid black;;">7757571</td><td style="text-align: right;border-top: 1px solid black;;">3.36</td><td style="text-align: right;border-top: 1px solid black;;">5/25/2011</td><td style="border-top: 1px solid black;;">Lab - TB Skin Test CPT 86580</td><td style="text-align: right;border-top: 1px solid black;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">7757571</td><td style="text-align: right;;">3.36</td><td style="text-align: right;;">5/25/2011</td><td style=";">Lab - TB Skin Test CPT 86580</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">58</td><td style="text-align: right;;">7757571</td><td style="text-align: right;;">3.36</td><td style="text-align: right;;">5/25/2011</td><td style=";">Lab - TB Skin Test CPT 86580</td><td style="text-align: right;;">3</td></tr></tbody></table><p style="width:6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Scratchpad</p><br /><br />

Under ACC ID, I want to get the AccCodeID associated with TrxID and Listing ID.

 

[T. Valko]

Sorry, for not posting this earlier...I had to figure out how to get the HTML to work (BTW, very easy to use).

Here's the raw data that I am trying to pull from:

Excel 2010<TABLE style="BORDER-RIGHT: #bbb 1px solid; BORDER-TOP: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BORDER-BOTTOM: #bbb 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #dae7f5; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD>TrxID</TD><TD>ListingID</TD><TD>TrxDate</TD><TD>AccCodeID</TD><TD>GLCode</TD><TD>GLFac</TD><TD>Amount</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">7757571</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">5/25/2011</TD><TD style="TEXT-ALIGN: right">58</TD><TD style="TEXT-ALIGN: right">325</TD><TD style="TEXT-ALIGN: right">$440.00 </TD><TD style="TEXT-ALIGN: right">$3.36 </TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">7757571</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">5/25/2011</TD><TD style="TEXT-ALIGN: right">589</TD><TD style="TEXT-ALIGN: right">325</TD><TD style="TEXT-ALIGN: right">$440.00 </TD><TD style="TEXT-ALIGN: right">$14.00 </TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">7757571</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">5/25/2011</TD><TD style="TEXT-ALIGN: right">411</TD><TD style="TEXT-ALIGN: right">325</TD><TD style="TEXT-ALIGN: right">$440.00 </TD><TD style="TEXT-ALIGN: right">$0.84 </TD></TR></TBODY></TABLE>

Account Report​

Here is where I am trying to pull it to:

Excel 2010<TABLE style="BORDER-RIGHT: #bbb 1px solid; BORDER-TOP: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BORDER-BOTTOM: #bbb 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #dae7f5; TEXT-ALIGN: center"><TH></TH><TH>EV</TH><TH>EW</TH><TH>EX</TH><TH>EY</TH><TH>EZ</TH><TH>FA</TH><TH>FB</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; BORDER-BOTTOM: black 1px solid">GL ID</TD><TD style="FONT-WEIGHT: bold; BORDER-BOTTOM: black 1px solid">ACC ID</TD><TD style="FONT-WEIGHT: bold; BORDER-BOTTOM: black 1px solid">TrxID</TD><TD style="FONT-WEIGHT: bold; BORDER-BOTTOM: black 1px solid">Amount</TD><TD style="FONT-WEIGHT: bold; BORDER-BOTTOM: black 1px solid">TrxDate</TD><TD style="FONT-WEIGHT: bold; BORDER-BOTTOM: black 1px solid">Description</TD><TD style="FONT-WEIGHT: bold; BORDER-BOTTOM: black 1px solid">Listing ID</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right">325</TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right">58</TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right">7757571</TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right">3.36</TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right">5/25/2011</TD><TD style="BORDER-TOP: black 1px solid">Lab - TB Skin Test CPT 86580</TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">#VALUE!</TD><TD style="TEXT-ALIGN: right">7757571</TD><TD style="TEXT-ALIGN: right">3.36</TD><TD style="TEXT-ALIGN: right">5/25/2011</TD><TD>Lab - TB Skin Test CPT 86580</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">58</TD><TD style="TEXT-ALIGN: right">7757571</TD><TD style="TEXT-ALIGN: right">3.36</TD><TD style="TEXT-ALIGN: right">5/25/2011</TD><TD>Lab - TB Skin Test CPT 86580</TD><TD style="TEXT-ALIGN: right">3</TD></TR></TBODY></TABLE>

Scratchpad​

Under ACC ID, I want to get the AccCodeID associated with TrxID and Listing ID.

One way...

Array entered**:

=INDEX(D$2:D$10,MATCH(1,IF(A$2:A$10=EX2,IF(B$2:B$10=FB2,1)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down as needed.

 

[nkasper]

Thanks so much! It worked wonderfully...Now at the risk of sounding stupid, can you clarify what exactly Match is doing here? I am not familiar with this, and don't understand how the nesting of if's inside the Match function is working.

 

[T. Valko]

Thanks so much! It worked wonderfully...Now at the risk of sounding stupid, can you clarify what exactly Match is doing here? I am not familiar with this, and don't understand how the nesting of if's inside the Match function is working.

We're looking for 2 lookup values:

IF(A$2:A$10=EX2

IF(B$2:B$10=FB2

Those expressions will return an array of either TRUE or FALSE.

Where both conditions are TRUE we "mark" that location with a 1. It might look something like this:

A2 = EX2 = T.....B2 = FB2 = F = F
A3 = EX2 = F.....B3 = FB2 = F = F
A4 = EX2 = T.....B4 = FB2 = T = 1
A5 = EX2 = F.....B5 = FB2 = T = F

Then we look for the result that corresponds to 1:

INDEX(D2:D5,MATCH(1,{F;F;1;F},0))

MATCH(1,{F;F;1;F},0) = 3

So:

INDEX(D2:D5,3) = whatever is in cell D4

 

[nkasper]

Thanks so much...this really helps me understand it! I really appreciate all the help that you gave me on this.

 

[T. Valko]

Thanks so much...this really helps me understand it! I really appreciate all the help that you gave me on this.

You're welcome. Thanks for the feedback!