one lookup value, multiple matches

[dev111ski]

Hi,

What I am basically trying to do is to find a formula which will show the repairs for a property and list all of them.

Where I have tried to use a formula is in the statement sheet with a border round it.

Using the first address (1a Westfield Road), I want to list all the repairs that have been made on that property.

In the repairs sheet, in cell Y2 is the count for the number of times the first address in the statement sheet appears in the repairs sheet.

I want the formula to do this to be where #VALUE currently is in the statement sheet.

I'd be really grateful for any help on this :D

***Sorry for posting this twice but my internet messed up when I originally submitted and didn't think that it worked

Thanks

[npamcpp]

Your question is not clear.

Where do you want to see a result? What is the input? What is the processing logic? What should the output look like?

Please post a file with a mocked up example of the expected result and, please, make it crystal clear what you want to see where. You know your workbook inside out. We don't. So you need to show us what you want to see. Enter the desires results manually and explain the logic of how to get there.

Don't just post a workbook and hope that people will figure out how it all hangs together.

[dev111ski]

What I am aiming to do is list all the repairs of a single property in the statement sheet where the #VALUE is shown.

What I am basically trying to achieve: [lookup first address, find repairs linked to this address, list repairs in statement sheet]

For 1a Westfield Road, I want to list the following:

Gas certificate
gas certifcate
Issue electrical certificate

What I have currently tried is the same formula which I used to list all the addresses that a landlord owns, but comes up with #VALUE

I hope I have made it more clear in what I am trying to do.

[benishiryo]

is this similar to what u are lookin for?
http://www.get-digital-help.com/2009...okup-in-excel/

let us know if u cant apply what u see there.

[dev111ski]

I've changed the formula for what I am trying to do and it's still coming up with #VALUE

[npamcpp]

Every time I open your workbook, my computer goes into overdrive and calculates for two minutes before settling down again. There's something seriously wrong with your file.

I still don't see what you want to achieve. You say >> "list all the repairs performed on this property" but you point to a single cell? Huh? Where do you want that list?

You cannot use formulas to loop through a list of values and produce individual accounts on a single sheet.

Step back a few paces. We don't know your workbook (and it's a pita to work with).

As I suggested before, mock up the results you want to see. That is: manually type in what you would like to see as the result. Then at least we know what to drive at.

And do something to make your spreadsheet not so processor intense. It has links to two other workbooks, "Test data.xlsx" and "Tracking All2012.xlsx" -- since those links are not included in your file things are quite slow to process when downloading your sample file.

[dev111ski]

Ok, scrap the workbook.

I don't want the list in one cell, that's just where I want te list to start.

The first property is 1a Westfield. I want to have a list of the repairs made in that property, so I want something like this:

Property Maintenance
1a Westfield Road	Gas certificate
	gas certificate
	Issue electrical certificate

Has that made it more clear?

[npamcpp]

why on earth would you post that as a Word file? How can anyone test an Excel formula suggestion in a Word file?

Why do you make it so hard for us to help you?

Tuning out. I'm not in the mood for 20 questions.

[dev111ski]

I managed to fix it myself with no help from you npamcpp

Thanks benishiryo for your link, it helped me figure it out I've given you a star :D