Not Solved: Formula to match 1 or 2 columns and then from a random range upon matching

Attached a new sample spreadsheet with examples, Stop removing sample fake data spreadsheet. Please read.

ALL DATA IS FAKE from an online data generator. The Cities and Provinces are real, from the Government of Canada, the branches are from TD Bank and are public to add authenticity for testing purposes, all DATA IS NOT REAL, IT IS FAKE.

CREDIT CARD NUMBERS FAKE, CVV FAKE. Hopefully people will read before removing sample fake data worksheet.

NAMES AND ADDRESSES GENERATED FROM A FAKE GENERATOR, REMOVED OUT OF SAMPLE AS NOT NEEDED. PM me as I created a code that combines First and Last Name, has modifiers between including "-", "_" and "." as well as 12 different numbers, and 13 different email service providers. Unable to post here as I am too new and it keeps giving me an error in my submission.

It is the Routing Number and Transit number I am looking for in the Transactions Worksheet.

STATING IT AGAIN, ALL DATA IS FAKE

Excel spreadsheet with over 50,000 rows, two worksheets, first one is called Transactions, second is called Branches

I am trying to find the routing number and transit number where the Province and City match, however:

IF the Province matches and the City is not found, a random city chosen, if there are multiple branches in that city, then a random branch is pulled.

IF the Province and the City match, a random branch is chosen if there is more than 1 branch in the city.

Named Ranges: each Province has a named Range in Branches: BRITISHCOLUMBIA,ALBERTA,SASKATCHEWAN


Column Routing/Transit Number is blank in the Transactions worksheet, a random Branch would come from the Branches worksheet

A random Branch would be generated from the Province of British Columbia and the City matching Vancouver, this City has 81 Branches, so let's say randomly it pulls the branch located at Coal Harbour, 494400-94400 would be pulled into the column*Routing/Transit Number in the Transactions Worksheet.


I tried, #REF error, so I know I have it wrong. Tried VLOOKUP, but want to be able to choose a RANDOM branch if the City has more than one/

*=INDEX(INDIRECT(VLOOKUP(G2,ALLPROVINCES,'Branches'!$D$2:$F$2526,4,FALSE)),RANDBETWEEN(1,COUNTA(INDIRECT(VLOOKUP(D2,ALLPROVINCES$D$2:$D$2526,2,FALSE)))))

your description, a bit long and a bit convoluted, doesn't match your workbook. col F in both tabs contain a city. There is no formula in you workbook. the "example" you mention about Anne and randomly pulling "Coal Harbour", Coal Harbour is not in either workbook, neither is Anne.
Perhaps you could upload another workbook that not only contains examples matching your description you could also include an example of your expected results and where they would come from.

ecomkid I have removed your attachment as it appears to contain real personal data.

If the data is actually dummy data, then please repost. otherwise replace the real data with dummy data. This is a public forum and is not the place to post real personal; data.

We have a duty to protect real people, and there was no way of knowing that this data was fake. Had you told us in the opening post that this data was artificially generated, then it would have been fine. Pleae bear this in mind in future.

You can reattach the workbook to your post.

STATED IN FIRST PARAGRAPH
Generating fake data for our software, the Users, Addresses, Credit Card numbers, Postal Codes, CVV, Amounts, and Provinces are fake, the Cities were generated from real data pulled from the Government of Canada.

Point taken, but at least three of us missed it completely (one mod, one member who reported it and was concerened, and me). Your opening post is very wordy (nothing wrong with that), so important information like that needs to stand out.

I'll have a look at your workbook now.

EDIT: Your sample workbook is missing expected results - please fill these in manually for 5-10 rows in the results sheet so that we can see what we are aiming for. Thanks.

Sorry, but there are still no example results:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	K	L	M	N
1	Card Type	Card Number	CVV	TRANSACTION-ROUTING NUMBER
2	Visa	4539822499742680	633	?
3	Visa	4556584585542	354	?
4	Visa	4916763263545	296	?
5	Visa	4929783393457	503	?

Sheet: Transactions

Seeing what you are aiming for will help to consolidate what you are describing in your opening post.

Added examples in worksheet with explanation, hope this helps. Thank you.

Thanks - I'll have another look shortly.

In your VM to me you shared this formula that you used to generate fake E-mail addresses. I am sharing it here, as it may be useful to others.

=A2&(CHOOSE(RANDBETWEEN(1,3),".","-","_")&B2&(CHOOSE(RANDBETWEEN(1,10),"123","456","789","223","445","4152","991","862","742","6234")&"@"&(CHOOSE(RANDBETWEEN(1,10),"aol.com","gmail.com","gmx.com","icloud.com","hotmail.com","outlook.com","protonmail.com","yandex.com","zoho.com","zohomail.com")))

I've looked at your workbook - that's much clearer now, thanks. I'm offline for a bit, but will look in again later unless someone else chips in.

Originally Posted by ecomkid

DATA IS FAKE

Credit card numbers generated from Generator data.
People and addresses generated from a name and address generator

Cities and Provinces real, TD branches real from a public site.

Please stop removing sample data, which was only to clarify the problem on the page.

Wind your neck in, kid. I stated quite clearly...."If the data is actually dummy data, then please repost. otherwise replace the real data with dummy data."
All you needed to do is calmly state that it was dummy data, and then repost, as suggested.

Try this as an array formula in N2 and drag down. I've put it in column P for now in the attached spreadsheet so you can see it in your format

Please Login or Register  to view this content.

Quick amendment to above. Use this to pick up the instances where there are no matches for either of the two columns. Still as an array formula

Please Login or Register  to view this content.