If(And) with a Vlookup

[0267Kim]

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.

 

[Special-K99]

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?

 

[0267Kim]

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]

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]

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]

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]

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

 

[0267Kim]

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"

Book1
	A	B	C	D
1	Bank Account	Store	Transaction Type	Tender Type
2	DEB01	12222	Payment	Bank Card
3	DA01	12222	Payment	Cash
4	DA01	12222	Payment	Cash
5	DA01	12222	Payment	Cash
6	DEB01	12222	Pay Off	Bank Card
7	DA01	12222	Sale	Cashier's Check
8	DA01	12222	Payment	Cash
9	DEB01	12222	Payment	Bank Card
10	DEB01	12222	Payment	Bank Card
Cash Sheet

Cell Formulas
Range	Formula
A2	=IF(AND(D2="Cash",C2="Payment"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Cashier's Check",C2="Payment"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Money Order",C2="Payment"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Bank Card",C2="Payment"),VLOOKUP(B2,'Store Report'!A:J,4,0),IF(AND(D2="Card (Auto)",C2="Payment"),VLOOKUP(B2,'Store Report'!A:J,5,0),IF(AND(D2="Posted Collections",C2="Payment"),VLOOKUP(B2,'Store Report'!A:J,6,0),IF(AND(D2="Western Union",C2="Payment"),VLOOKUP(B2,'Store Report'!A:J,8,0),IF(AND(D2="ACH",C2="Payment"),VLOOKUP(B2,'Store Report'!A:J,3,0),IF(AND(D2="Trustee Payment",C2="Payment"),VLOOKUP(B2,'Store Report'!A:J,7,0),IF(AND(D2="Bank Card",C2="Payment - Void"),VLOOKUP(B2,'Store Report'!A:J,4,0),IF(AND(D2="Card (Auto)",C2="Payment - Void"),VLOOKUP(B2,'Store Report'!A:J,5,0),IF(AND(D2="Cash",C2="Payment - Void"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Cashier's Check",C2="Payment - Void"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Money Order",C2="Payment - Void"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Card (Auto)",C2="Payment - Void"),VLOOKUP(B2,'Store Report'!A:J,5,0),IF(AND(D2="Posted Collections",C2="Payment - Void"),VLOOKUP(B2,'Store Report'!A:J,6,0),IF(AND(D2="ACH",C2="Payment - Void"),VLOOKUP(B2,'Store Report'!A:J,3,0),IF(AND(D2="Trustee Payment",C2="Payment - Void"),VLOOKUP(B2,'Store Report'!A:J,7,0),IF(AND(D2="ACH",C2="Payment- Returned"),VLOOKUP(B2,'Store Report'!A:J,3,0),IF(AND(D2="Cash",C2="Pay Off"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Money Order",C2="Pay Off"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Cashier's Check",C2="Pay Off"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Money Order",C2="Pay Off"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Bank Card",C2="Pay Off"),VLOOKUP(B2,'Store Report'!A:J,4,0),IF(AND(D2="Card (Auto)",C2="Pay Off"),VLOOKUP(B2,'Store Report'!A:J,5,0),IF(AND(D2="Posted Collections",C2="Pay Off"),VLOOKUP(B2,'Store Report'!A:J,6,0),IF(AND(D2="Western Union",C2="Pay Off"),VLOOKUP(B2,'Store Report'!A:J,8,0),IF(AND(D2="Trustee Payment",C2="Pay Off"),VLOOKUP(B2,'Store Report'!A:J,7,0),IF(AND(D2="Bank Card",C2="Pay Off - Void"),VLOOKUP(B2,'Store Report'!A:J,4,0),IF(AND(D2="Cash",C2="Pay Off - Void"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Cashier's Check",C2="Pay Off - Void"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Money Order",C2="Pay Off - Void"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Cash",C2="Rescind"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Cashier's Check",C2="Rescind"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Bank Card",C2="Rescind"),VLOOKUP(B2,'Store Report'!A:J,4,0),IF(AND(D2="Cashier's Check",C2="Rescind - Void"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Cash",C2="NSF Prepayment"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Bank Card",C2="NSF Prepayment"),VLOOKUP(B2,'Store Report'!A:J,4,0),IF(AND(D2="Card (Auto)",C2="NSF Prepayment"),VLOOKUP(B2,'Store Report'!A:J,5,0),IF(AND(D2="Cashier's Check",C2="NSF Prepayment"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Cash",C2="Sale"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Cashier's Check",C2="Sale"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Money Order",C2="Sale"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Bank Card",C2="Sale"),VLOOKUP(B2,'Store Report'!A:J,4,0),IF(AND(D2="Posted Collections",C2="Sale"),VLOOKUP(B2,'Store Report'!A:J,6,0),IF(AND(D2="Bank Card",C2="Sale - Void"),VLOOKUP(B2,'Store Report'!A:J,4,0),IF(AND(D2="Cashier's Check",C2="Sale - Void"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Money Order",C2="Sale - Void"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Posted Collections",C2="Sale - Void"),VLOOKUP(B2,'Store Report'!A:J,6,0),IF(AND(D2="Check",C2="Refund"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Check",C2="Refund - Void"),VLOOKUP(B2,'Store Report'!A:J,2,0),IF(AND(D2="Check",C2="Advance"),VLOOKUP(B2,'Store Report'!A:J,10,0),IF(AND(D2="Western Union",C2="Advance"),VLOOKUP(B2,'Store Report'!A:J,10,0),IF(AND(D2="Check",C2="Advance - Void"),VLOOKUP(B2,'Store Report'!A:J,10,0),IF(AND(D2="Check",C2="Refinance"),VLOOKUP(B2,'Store Report'!A:J,10,0),IF(AND(D2="Check",C2="Refinance - Void"),VLOOKUP(B2,'Store Report'!A:J,10,0),IF(AND(D2="Check",C2="Refinance/Add-on"),VLOOKUP(B2,'Store Report'!A:J,10,0),IF(AND(D2="Western Union",C2="Refinance/Add-on"),VLOOKUP(B2,'Store Report'!A:J,8,0),IF(AND(D2="Check",C2="Refinance/Add-on - Void"),VLOOKUP(B2,'Store Report'!A:J,10,0),IF(AND(D2="ACH",C2="ACH Payment Clear"),VLOOKUP(B2,'Store Report'!A:J,3,0),IF(AND(D2="ACH",C2="ACH Payment Clear - Void"),VLOOKUP(B2,'Store Report'!A:J,3,0),IF(AND(D2="ACH",C2="ACH Prepayment Applied"),VLOOKUP(B2,'Store Report'!A:J,3,0),IF(AND(D2="ACH",C2="ACH Payment Returned"),VLOOKUP(B2,'Store Report'!A:J,3,0),IF(AND(D2="ACH",C2="ACH Prepayment Returned"),VLOOKUP(B2,'Store Report'!A:J,3,0),"No"))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

Sheet 2 "Store Report"
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E	F	G	H	I	J
1	1	2	3	4	5	6	7	8	9	10
2	Store Code	Deposit Account	ACH (Personal Loan)	Debit Account	Debit Card (Auto)	Posted Collections	Trustee Account	Western Union Account	Expense Account	Loan Account
3	12222	DA01		DEB01	MODEB01	PC04	TA01	WU01	EA01	LA01
4	12225	DA02		DEB01	MODEB01	PC04	TA01	WU01	EA02	LA02
5	12229	DA01	ACH01	DEB01	MODEB01	PC04	TA01	WU01	EA01	LA04
6	12242	DA03		DEB02	MODEB01	PC04	TA01	WU01	EA02	LA02
7	12245	DA04		DEB02	MODEB01	PC04	TA01	WU01	EA03	LA05
8	12269	DA01		DEB03	MODEB01	PC04	TA01	WU01	EA04	LA01
9	12272	DA01	ACH01	DEB01	MODEB01	PC04	TA01	WU01	EA04	LA01
10	12282	DA02		DEB03	MODEB01	PC04	TA01	WU01	EA05	LA03
11	12286	DA05		DEB01	MODEB01	PC04	TA01	WU01	EA07	LA07
12	12289	DA01	ACH01	DEB07	MODEB01	PC04	TA01		EA09	LA09
13
Store Report

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]

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.

 

[0267Kim]

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!