VLOOKUP Help - Returning Multiple Rows Based On One Keyword

[RandomlySet]

Hi all,

I'm creating a spreadsheet to keep track of my house finances.

I have a tab for each bank account we have set up, these are:
Joint Account
Holiday Fund
Additional Savings
Car Savings
Holiday Fund
Retirement

What I intend to do is input all income and outgoings in 1 tab (the Joint Account tab).

All tabs are set up the same. They have the following columns:
Description (super market shop, coffee, electric bill, holiday fund etc)
Type (savings, bill, leisure)
Date
In
Out



So far, I have created a VLOOKUP within the Holiday Fund tab so that it searches columns B:F and returns all values from the Joint Account sheet, so basically it's a replication. The only difference being, the value under the "Out" column on the Joing Account sheet will appear in the "In" column within the Holiday Fund sheet. My forumula looks like this: =VLOOKUP("Holiday Fund",'Joint Account'!$B:$F,1,FALSE)

That works fine. However, there will be more than 1 row that has holiday fund inputted as this sheet will be for all of 2013. The code will only return the 1st result found, and then stop.

How do I make it return all rows where "Holiday Fund" is mentioned in a cell?

[TMS]

What's the point of having multiple sheets with the same data? With the same layout? Pain in the proverbial to keep aligned.

Why not just record everything once in an input sheet. If necessary, add an additional column that would identify the category ... that is, the sheet you would have copied the data to.

Then, you can filter and print specific categories ... and you can select more than one category if you need to. Unfiltered, you can see the "big picture".

With the data in one sheet, you can create a Table; you can sort the data by date, category, whatever; you can analyse the data in a Pivot Table; you can display the data graphically in a Chart; and you can use Excel most effectively.

Just my thoughts.

If you do split it up, you probably need to look at SUMIF, SUMIFS, COUNTIF, COUNTIFS and so on.

Regards, TMS

[abousetta]

Hello and welcome to the forum,

Just as a heads-up, you will get faster and more precise results if you simplify your post to exactly what is needed. In your case, it's "How do I make (Vlookup) return all rows where "Holiday Fund" is mentioned in a cell?" For this there are several approaches. You can google vlookup + all matches (or + all instances or + all values) and you will find several approaches. These include solutions using formulas and vba approaches. If you can't implement one then I also suggest you upload a dummy copy of your workbook so that we can see the current workbookbook/ worksheet structure.

Hope this helps.

abousetta

[RandomlySet]

Thanks for that TMShucks. I work with Excel almost daily (at least 4 days a week) and have set up several workbooks up at work, and usually have multiple sheets, using lookups etc.

I think I may have been over-complicating things. You are probably right in just having everything within a single sheet....

[TMS]

It's a matter of personal choice. People often set up workbooks to reflect a paper or manual system, maybe even double entry book-keeping.

Personally, I advocate a simple single worksheet for raw data. Turn it into a table, sort it, filter it, pivot it, chart it, ... whatever. Use Data validation for consistent input. You then have no worries about looking up data, especially if it becomes complex. It is doable with formulae but it can be a lot of hard and, in my opinion, unnecessary work.

If you want to get sophisticated, you can use VBA for the "standard" filters.

Good luck with it.


Regards, TMS


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[TMS]

Thanks for the rep.