VLookup Help

[tbakbradley]

I'm not sure the VLookup feature is what I need. Below is what I'm after.

I have a Worksheet of Raw Data. This Raw Data will be updated weekly. Within this Raw Data I have two columns with Addresses (Address1 and Address2) that I want to Search (therefore, I would like my Lookup to search both columns at the same time if possible). I've also noticed that some of the addresses have extra spaces that could be a problem (e.g. 83500 Main St). Two spaces between 83500 and Main when another line item may show 83500 Main St. with one space.

ExampleProject # starts at A1)

Project # Revenue Date1 Date2 Date3 Address1 Address2
1 $1 83500 Main 123 Light
2 $5 83500 Main Y St
3 $10 A St B St
4

I would like to have a separate Worksheet that allows the user to type in the Address and a VLookup (or something) that will search both Address1 and Address2 (Columns F and G) on the other worksheet. When it finds the Address, I'd like it to then provide the Project Data and the Revenue (Column A and B).

Here is the tricky part. There will be thousands of rows, with many showing the same address. For everytime the address is found in the VLookup (or something), I need all the Project#s and Revenue amounts for each Project Case. Also, since there could be spelling issues in the address, I'd like to have some kind of wild card to where we could enter the address on the Lookup as "83500$Main$St$" incase there are multiple spaces between each section of the address as that is a manual input within the system the data is being pulled from.

Is this something that can be accomplished with one of the Lookup features, or would it involve a VB Macro? If a Macro, any help would be appreciated as I have experience with Macros...some complex, but by no means am I an expert.

Thanks for the help

 

[tbakbradley]

Looking over my post, just to make it more simple.

I don't need this to search "Two Columns" at the same time. Whatever function I use, I can use twice, but for the other column.

What I need, is a way to take an input value that is typed in to A1/Sheet1, look it up on the data from Sheet 2 and pull required data, but the Lookup valule will have MULTIPLE entries.

If the User types in "83500 Main St", that it pull multiple Rows of Data. The Data I need to pull won't necessarily be to the right of the Lookup data, but could be anywhere on the Spreadsheet.

Any assistance would be helpful. Thanks

 

[tbakbradley]

I tried this formula, and it worked on a simple data set I made as a test (but using B1:B20 and A1:A20 on the same Sheet instead of using Data!AZ and E).

$AZ$2:$AZ$5000 is the data I want returned from the "Data" Tab. $E2:$E$5000 is the data I'm searching on the "Data" Tab. $E$1 is the Input data from the user on Sheet1. This equation "should" bring over all data from AZ2-AZ5000 based on the Input. E2-E5000 on the Data Tab can have multiple "Addresses", so it pulls over all the info it finds in AZ. Again, I can get this to work by Data I manually built as a test, but will not work from the Data Extraction I have on the Data tab from our Database Query. I'm sure I'm just missing something

=INDEX(Data!$AZ$2:$AZ$5000,LARGE((Data!$E$2:$E$5000=$E$1)*ROW(Data!$E$2:$E$5000),COUNTIF(Data!$E$2:$E$5000,$E$1)+1-ROW(Data!E3)))