If(And) with a Vlookup

0267Kim

New Member
Joined
Aug 20, 2019
Messages
8
Hi everyone,

I have a situation where I've got a spreadsheet with 2 different columns of data that must match. If they match, I'll need to do a vlookup in order to find a specific account ID on another sheet.



What I want to do is from sheet 1, if Acct Type = "Deposit", and Tender Type = "Cash", then I want the function to do a Vlookup based on the store and sheet 2 . So for store 10001, the vlookup would look at column 2 and return deposit acct D1234, otherwise have another nested if statement.

I was thinking =IF(AND(Acct Type = "Deposit", Tender Type = "Cash"),vlookup(Sheet1!A3,Sheet2!E:G,2,FALSE),IF(AND(Acct Type = "Disbursement", Tender Type = "Check"...etc...

I keep getting an #N/A. Any help with the syntax would be greatly appreciated.

Thank you.
 
Last edited by a moderator:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,314
Re: Help with an If(And) with a Vlookup

#N/A means the VLOOKUP failed

It should be something along the lines of

IFERRO(VLOOKUP...IFERROR(VLOOKUP...))

You need to explain this fully

=IF(AND(Acct Type = "Deposit", Tender Type = "Cash"),vlookup(Sheet1!A3,Sheet2!E:G,2,FALSE),IF(AND(Acct Type = "Disbursement", Tender Type = "Check"...etc...

What happens if Disbursement and Check... ?

Are there other Account and tender Types you need to check for?
Are you searching the same range as Deposit and Cash or some other range?
 
Last edited:

0267Kim

New Member
Joined
Aug 20, 2019
Messages
8
Re: Help with an If(And) with a Vlookup

I was able to get this working. A Sample of the formula is below:

=IF(AND(R2="Cash",Q2="Payment"),VLOOKUP(G2,'TMX Store Report'!K:AE,12,0)

What I'm tying to do is say, If this and that, then vlookup store number in column 12 of other report. So, the criteria are Tender Type (here "Cash") and Transaction Type (here "Payment") and store. There are many possible combinations of Tender Type and Transaction Type and thousands of stores. Each combination will tell the Vlookup to go look at a specific column on the store report (here, column 12).

Here's a few more of the many combinations:




The issue I'm having now is with so many combinations of Tender and Transaction type and store, I've run into an error which says "This formula can't be entered because it uses more than 64 levels of nesting." Is there a way around this? I thought about combining all data points into a table and then using an index match, but literally, there are thousands of possible combinations.
 

0267Kim

New Member
Joined
Aug 20, 2019
Messages
8
Re: Help with an If(And) with a Vlookup

Sorry, my example data didn't take:

Transaction Type Tender Type Store
Payment ACH 100045
Payment Bank Card 100045
Payment Cash 100045
Payment Money Order 100045
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,314
Re: Help with an If(And) with a Vlookup

Yep if you have lots of nested condtions you should be looking at a a table with a straight lookup/INDEX( MATCH())
 

0267Kim

New Member
Joined
Aug 20, 2019
Messages
8
Re: Help with an If(And) with a Vlookup

I'm having trouble visualizing this.

The way it's currently set up is on Report 1, have the afore mentioned Store, Transaction Type, and Tender Type. One Report 2 I have Store, and Bank Acct1, Bank Acct2, Bank Acct3.

The result I want I the correct Bank Acct from Report 2 based on the Tender Type and Transaction Type from Report 1. With Store ID being on both Report 1 and Report 2.

I'm thinking my Index should be all possible combinations of Store, Trans Type, and Tender Type from Report 1. The Match would have to be where each combination of Store, Trans Type, and Tender Type is unique.

Then I would almost have to Vlookup again to get the proper Bank Acct from Report 2.

What am I missing?
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,314
Re: Help with an If(And) with a Vlookup

Urm nope not from your description.

"on Report 1, have the afore mentioned Store, Transaction Type, and Tender Type."
"On Report 2 I have Store, and Bank Acct1, Bank Acct2, Bank Acct3."

So the ONLY thing linking report 1 and report 2 is Store, NOT transaction type or tender type. Transaction and tender type are irrelevant since they dont appear on report 2 or you would have said they appear on both reports. What connects report1 and report 2 is the Store, and Store alone nothing else.

If this is not how your data looks then use the link below to display what the data for report1 and report2 actually look like.

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html
 
Last edited:

0267Kim

New Member
Joined
Aug 20, 2019
Messages
8
Re: Help with an If(And) with a Vlookup

Sorry for the delayed response. It took me a little while to figure out how to post up a sample. Hopefully this works!


Sheet 1 "Transaction Type and Tender Type"
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;border-right: 1px solid black;background-color: #BFBFBF;;">Bank Account</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #00529B;;">Store</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #00529B;;">Transaction Type</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #00529B;;">Tender Type</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-right: 1px solid black;background-color: #BFBFBF;;">DEB01</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F5F5F5;;">12222</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F5F5F5;;">Payment</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F5F5F5;;">Bank Card</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-right: 1px solid black;background-color: #BFBFBF;;">DA01</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">12222</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Payment</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Cash</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-right: 1px solid black;background-color: #BFBFBF;;">DA01</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">12222</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Payment</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Cash</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="border-right: 1px solid black;background-color: #BFBFBF;;">DA01</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F5F5F5;;">12222</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F5F5F5;;">Payment</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F5F5F5;;">Cash</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="border-right: 1px solid black;background-color: #BFBFBF;;">DEB01</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">12222</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Pay Off</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Bank Card</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="border-right: 1px solid black;background-color: #BFBFBF;;">DA01</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F5F5F5;;">12222</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F5F5F5;;">Sale</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F5F5F5;;">Cashier's Check</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="border-right: 1px solid black;background-color: #BFBFBF;;">DA01</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">12222</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Payment</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Cash</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="border-right: 1px solid black;background-color: #BFBFBF;;">DEB01</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F5F5F5;;">12222</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F5F5F5;;">Payment</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F5F5F5;;">Bank Card</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="border-right: 1px solid black;background-color: #BFBFBF;;">DEB01</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">12222</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Payment</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Bank Card</td></tr></tbody></table><p style="width:8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Cash Sheet</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A2</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">D2="Cash",C2="Payment"</font>),VLOOKUP(<font color="Red">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Red">AND(<font color="Green">D2="Cashier's Check",C2="Payment"</font>),VLOOKUP(<font color="Green">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Green">AND(<font color="Purple">D2="Money Order",C2="Payment"</font>),VLOOKUP(<font color="Purple">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Purple">AND(<font color="Teal">D2="Bank Card",C2="Payment"</font>),VLOOKUP(<font color="Teal">B2,'Store Report'!A:J,4,0</font>),IF(<font color="Teal">AND(<font color="#FF00FF">D2="Card (Auto)",C2="Payment"</font>),VLOOKUP(<font color="#FF00FF">B2,'Store Report'!A:J,5,0</font>),IF(<font color="#FF00FF">AND(<font color="Navy">D2="Posted Collections",C2="Payment"</font>),VLOOKUP(<font color="Navy">B2,'Store Report'!A:J,6,0</font>),IF(<font color="Navy">AND(<font color="Blue">D2="Western Union",C2="Payment"</font>),VLOOKUP(<font color="Blue">B2,'Store Report'!A:J,8,0</font>),IF(<font color="Blue">AND(<font color="Red">D2="ACH",C2="Payment"</font>),VLOOKUP(<font color="Red">B2,'Store Report'!A:J,3,0</font>),IF(<font color="Red">AND(<font color="Green">D2="Trustee Payment",C2="Payment"</font>),VLOOKUP(<font color="Green">B2,'Store Report'!A:J,7,0</font>),IF(<font color="Green">AND(<font color="Purple">D2="Bank Card",C2="Payment - Void"</font>),VLOOKUP(<font color="Purple">B2,'Store Report'!A:J,4,0</font>),IF(<font color="Purple">AND(<font color="Teal">D2="Card (Auto)",C2="Payment - Void"</font>),VLOOKUP(<font color="Teal">B2,'Store Report'!A:J,5,0</font>),IF(<font color="Teal">AND(<font color="#FF00FF">D2="Cash",C2="Payment - Void"</font>),VLOOKUP(<font color="#FF00FF">B2,'Store Report'!A:J,2,0</font>),IF(<font color="#FF00FF">AND(<font color="Navy">D2="Cashier's Check",C2="Payment - Void"</font>),VLOOKUP(<font color="Navy">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Navy">AND(<font color="Blue">D2="Money Order",C2="Payment - Void"</font>),VLOOKUP(<font color="Blue">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Blue">AND(<font color="Red">D2="Card (Auto)",C2="Payment - Void"</font>),VLOOKUP(<font color="Red">B2,'Store Report'!A:J,5,0</font>),IF(<font color="Red">AND(<font color="Green">D2="Posted Collections",C2="Payment - Void"</font>),VLOOKUP(<font color="Green">B2,'Store Report'!A:J,6,0</font>),IF(<font color="Green">AND(<font color="Purple">D2="ACH",C2="Payment - Void"</font>),VLOOKUP(<font color="Purple">B2,'Store Report'!A:J,3,0</font>),IF(<font color="Purple">AND(<font color="Teal">D2="Trustee Payment",C2="Payment - Void"</font>),VLOOKUP(<font color="Teal">B2,'Store Report'!A:J,7,0</font>),IF(<font color="Teal">AND(<font color="#FF00FF">D2="ACH",C2="Payment- Returned"</font>),VLOOKUP(<font color="#FF00FF">B2,'Store Report'!A:J,3,0</font>),IF(<font color="#FF00FF">AND(<font color="Navy">D2="Cash",C2="Pay Off"</font>),VLOOKUP(<font color="Navy">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Navy">AND(<font color="Blue">D2="Money Order",C2="Pay Off"</font>),VLOOKUP(<font color="Blue">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Blue">AND(<font color="Red">D2="Cashier's Check",C2="Pay Off"</font>),VLOOKUP(<font color="Red">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Red">AND(<font color="Green">D2="Money Order",C2="Pay Off"</font>),VLOOKUP(<font color="Green">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Green">AND(<font color="Purple">D2="Bank Card",C2="Pay Off"</font>),VLOOKUP(<font color="Purple">B2,'Store Report'!A:J,4,0</font>),IF(<font color="Purple">AND(<font color="Teal">D2="Card (Auto)",C2="Pay Off"</font>),VLOOKUP(<font color="Teal">B2,'Store Report'!A:J,5,0</font>),IF(<font color="Teal">AND(<font color="#FF00FF">D2="Posted Collections",C2="Pay Off"</font>),VLOOKUP(<font color="#FF00FF">B2,'Store Report'!A:J,6,0</font>),IF(<font color="#FF00FF">AND(<font color="Navy">D2="Western Union",C2="Pay Off"</font>),VLOOKUP(<font color="Navy">B2,'Store Report'!A:J,8,0</font>),IF(<font color="Navy">AND(<font color="Blue">D2="Trustee Payment",C2="Pay Off"</font>),VLOOKUP(<font color="Blue">B2,'Store Report'!A:J,7,0</font>),IF(<font color="Blue">AND(<font color="Red">D2="Bank Card",C2="Pay Off - Void"</font>),VLOOKUP(<font color="Red">B2,'Store Report'!A:J,4,0</font>),IF(<font color="Red">AND(<font color="Green">D2="Cash",C2="Pay Off - Void"</font>),VLOOKUP(<font color="Green">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Green">AND(<font color="Purple">D2="Cashier's Check",C2="Pay Off - Void"</font>),VLOOKUP(<font color="Purple">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Purple">AND(<font color="Teal">D2="Money Order",C2="Pay Off - Void"</font>),VLOOKUP(<font color="Teal">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Teal">AND(<font color="#FF00FF">D2="Cash",C2="Rescind"</font>),VLOOKUP(<font color="#FF00FF">B2,'Store Report'!A:J,2,0</font>),IF(<font color="#FF00FF">AND(<font color="Navy">D2="Cashier's Check",C2="Rescind"</font>),VLOOKUP(<font color="Navy">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Navy">AND(<font color="Blue">D2="Bank Card",C2="Rescind"</font>),VLOOKUP(<font color="Blue">B2,'Store Report'!A:J,4,0</font>),IF(<font color="Blue">AND(<font color="Red">D2="Cashier's Check",C2="Rescind - Void"</font>),VLOOKUP(<font color="Red">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Red">AND(<font color="Green">D2="Cash",C2="NSF Prepayment"</font>),VLOOKUP(<font color="Green">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Green">AND(<font color="Purple">D2="Bank Card",C2="NSF Prepayment"</font>),VLOOKUP(<font color="Purple">B2,'Store Report'!A:J,4,0</font>),IF(<font color="Purple">AND(<font color="Teal">D2="Card (Auto)",C2="NSF Prepayment"</font>),VLOOKUP(<font color="Teal">B2,'Store Report'!A:J,5,0</font>),IF(<font color="Teal">AND(<font color="#FF00FF">D2="Cashier's Check",C2="NSF Prepayment"</font>),VLOOKUP(<font color="#FF00FF">B2,'Store Report'!A:J,2,0</font>),IF(<font color="#FF00FF">AND(<font color="Navy">D2="Cash",C2="Sale"</font>),VLOOKUP(<font color="Navy">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Navy">AND(<font color="Blue">D2="Cashier's Check",C2="Sale"</font>),VLOOKUP(<font color="Blue">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Blue">AND(<font color="Red">D2="Money Order",C2="Sale"</font>),VLOOKUP(<font color="Red">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Red">AND(<font color="Green">D2="Bank Card",C2="Sale"</font>),VLOOKUP(<font color="Green">B2,'Store Report'!A:J,4,0</font>),IF(<font color="Green">AND(<font color="Purple">D2="Posted Collections",C2="Sale"</font>),VLOOKUP(<font color="Purple">B2,'Store Report'!A:J,6,0</font>),IF(<font color="Purple">AND(<font color="Teal">D2="Bank Card",C2="Sale - Void"</font>),VLOOKUP(<font color="Teal">B2,'Store Report'!A:J,4,0</font>),IF(<font color="Teal">AND(<font color="#FF00FF">D2="Cashier's Check",C2="Sale - Void"</font>),VLOOKUP(<font color="#FF00FF">B2,'Store Report'!A:J,2,0</font>),IF(<font color="#FF00FF">AND(<font color="Navy">D2="Money Order",C2="Sale - Void"</font>),VLOOKUP(<font color="Navy">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Navy">AND(<font color="Blue">D2="Posted Collections",C2="Sale - Void"</font>),VLOOKUP(<font color="Blue">B2,'Store Report'!A:J,6,0</font>),IF(<font color="Blue">AND(<font color="Red">D2="Check",C2="Refund"</font>),VLOOKUP(<font color="Red">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Red">AND(<font color="Green">D2="Check",C2="Refund - Void"</font>),VLOOKUP(<font color="Green">B2,'Store Report'!A:J,2,0</font>),IF(<font color="Green">AND(<font color="Purple">D2="Check",C2="Advance"</font>),VLOOKUP(<font color="Purple">B2,'Store Report'!A:J,10,0</font>),IF(<font color="Purple">AND(<font color="Teal">D2="Western Union",C2="Advance"</font>),VLOOKUP(<font color="Teal">B2,'Store Report'!A:J,10,0</font>),IF(<font color="Teal">AND(<font color="#FF00FF">D2="Check",C2="Advance - Void"</font>),VLOOKUP(<font color="#FF00FF">B2,'Store Report'!A:J,10,0</font>),IF(<font color="#FF00FF">AND(<font color="Navy">D2="Check",C2="Refinance"</font>),VLOOKUP(<font color="Navy">B2,'Store Report'!A:J,10,0</font>),IF(<font color="Navy">AND(<font color="Blue">D2="Check",C2="Refinance - Void"</font>),VLOOKUP(<font color="Blue">B2,'Store Report'!A:J,10,0</font>),IF(<font color="Blue">AND(<font color="Red">D2="Check",C2="Refinance/Add-on"</font>),VLOOKUP(<font color="Red">B2,'Store Report'!A:J,10,0</font>),IF(<font color="Red">AND(<font color="Green">D2="Western Union",C2="Refinance/Add-on"</font>),VLOOKUP(<font color="Green">B2,'Store Report'!A:J,8,0</font>),IF(<font color="Green">AND(<font color="Purple">D2="Check",C2="Refinance/Add-on - Void"</font>),VLOOKUP(<font color="Purple">B2,'Store Report'!A:J,10,0</font>),IF(<font color="Purple">AND(<font color="Teal">D2="ACH",C2="ACH Payment Clear"</font>),VLOOKUP(<font color="Teal">B2,'Store Report'!A:J,3,0</font>),IF(<font color="Teal">AND(<font color="#FF00FF">D2="ACH",C2="ACH Payment Clear - Void"</font>),VLOOKUP(<font color="#FF00FF">B2,'Store Report'!A:J,3,0</font>),IF(<font color="#FF00FF">AND(<font color="Navy">D2="ACH",C2="ACH Prepayment Applied"</font>),VLOOKUP(<font color="Navy">B2,'Store Report'!A:J,3,0</font>),IF(<font color="Navy">AND(<font color="Blue">D2="ACH",C2="ACH Payment Returned"</font>),VLOOKUP(<font color="Blue">B2,'Store Report'!A:J,3,0</font>),IF(<font color="Blue">AND(<font color="Red">D2="ACH",C2="ACH Prepayment Returned"</font>),VLOOKUP(<font color="Red">B2,'Store Report'!A:J,3,0</font>),"No"</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Sheet 2 "Store Report"
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;border-bottom: 1px solid black;;">1</td><td style="text-align: right;border-bottom: 1px solid black;;">2</td><td style="text-align: right;border-bottom: 1px solid black;;">3</td><td style="text-align: right;border-bottom: 1px solid black;;">4</td><td style="text-align: right;border-bottom: 1px solid black;;">5</td><td style="text-align: right;border-bottom: 1px solid black;;">6</td><td style="text-align: right;border-bottom: 1px solid black;;">7</td><td style="text-align: right;border-bottom: 1px solid black;;">8</td><td style="text-align: right;border-bottom: 1px solid black;;">9</td><td style="text-align: right;border-bottom: 1px solid black;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Store Code</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Deposit Account</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">ACH (Personal Loan)</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Debit Account</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Debit Card (Auto)</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Posted Collections</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Trustee Account</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Western Union Account</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Expense Account</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Loan Account</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12222</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DA01</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DEB01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">MODEB01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">PC04</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TA01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">WU01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">EA01</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">LA01</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12225</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DA02</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DEB01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">MODEB01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">PC04</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TA01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">WU01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">EA02</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">LA02</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12229</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DA01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">ACH01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DEB01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">MODEB01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">PC04</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TA01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">WU01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">EA01</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">LA04</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12242</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DA03</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DEB02</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">MODEB01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">PC04</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TA01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">WU01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">EA02</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">LA02</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12245</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DA04</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DEB02</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">MODEB01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">PC04</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TA01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">WU01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">EA03</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">LA05</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12269</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DA01</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DEB03</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">MODEB01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">PC04</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TA01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">WU01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">EA04</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">LA01</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12272</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DA01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">ACH01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DEB01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">MODEB01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">PC04</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TA01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">WU01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">EA04</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">LA01</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12282</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DA02</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DEB03</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">MODEB01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">PC04</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TA01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">WU01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">EA05</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">LA03</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12286</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DA05</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DEB01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">MODEB01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">PC04</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TA01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">WU01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">EA07</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">LA07</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12289</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DA01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">ACH01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DEB07</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">MODEB01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">PC04</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TA01</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">EA09</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">LA09</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr></tbody></table><p style="width:9.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Store Report</p><br /><br />

I'm trying to populate the account account number in column 1 of the "Transaction Type and Tender Type" sheet using the "Store Report" information. I'm limited to 64 nested if statements.


[/FONT]
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,314
Re: Help with an If(And) with a Vlookup

Once you nest more than around 6 IF statements you should really be looking at constructing a table.

All your IFs staments consist of IF(AND(D2= something, C2= something), VLOOKUP(B2, 'Store Report'!A:J, column number, 0)
So...

in Sheet 2!A1:C64 construct a table, 64 rows by 3 columns wide

in column A put all the D2 condition text from your IF statments so reading down column A it should read

Cash
Cashier's Check
Money Order
etc

in Column B put all the C2 = condition text from your IF statements, so reading down column B it should read
Payment
Payment
Payment
etc

in Column C put the column number you want to retrieve from your IF statements so it should read
2
2
2
4
5
6
etc

Then replace that awful formula with

=IFERROR(VLOOKUP(B2,'Store Report'!A:J,INDEX(Sheet2!C$1:C$1000,MATCH(D2,IF(C2=Sheet2!B$1:B1000,Sheet2!A$1:A$1000)),1),0),"No")
Array formula, use Ctrl-Shift-Enter

If you need to add further conditions just enter more data into the table as the formula allows up to 1000 combinations
You just need the text for the D2 condition, text for the C2 condition and the column number to retrieve
No need to change the formula.
 
Last edited:

0267Kim

New Member
Joined
Aug 20, 2019
Messages
8
Re: Help with an If(And) with a Vlookup

Wow, that actually worked. Good bye to the limited 64 nested if statements. Thanks for your help Special K. I've never seen an if statement nested in an index(match) like that. Super Cool! Thanks again. This topic can be closed!
 

Forum statistics

Threads
1,078,447
Messages
5,340,345
Members
399,370
Latest member
salamon

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top