Function other than VLookup

Topic

New Reply

Hi, I attempted to use the V-Lookup function to identify the funds of Tables 1 and 2 with the same name. I attempted to place the Funds of Table 2 into the columns of Table 1. Unfortunately, V-Lookup only pulls the data of the first Match it sees. I have many funds that need to be matched to single occurrences of names.

Any other functions available?

Table 1
Name Fund Amount
BHAGAT 000001 2,316.38

Table 2
Source Name FUND Balance
Fund Summary Bhagat 554102 25000
Fund Summary Bhagat 554714 251.65

Reply | Quote

Replies

I am not sure what you are trying to do.

You only have one cell adjacent to the names in Column F of table 2. So how/where do you want to represent the other Funds ? As always, an example of how you’d like it to look would be most helpful.

PS If you were seeking to total the funds held by each person, that would be easy !

Reply | Quote

Hi, Martin, thanks for getting back to me. Below is what I’d like to see: the fund numbers from table 2 matched to the appropriate names from Table 1.

My project is to transfer dollars from the 00001 (general) fund numbers and allocate to the various funds in table 2.

Names Fund Amount Fund assigned per Name
BHAGAT 000001 2,316.38 554102 554714
BONNEY 000001 3,666.67 554710 556627 556352
CARBERRY 000001 5,991.34 549666 559417 554266 552095

Reply | Quote

OK, working on it.
Meantime, where did the 2316.38 next to BHAGAT come from – it isn’t the sum of the funds against BHAGAT in Table 2.

Reply | Quote

The 2,316.38 is the balance that I must allocate into the funds. It’s not a sum balance.

So, there’s no other way around this other than using a Macro? I don’t use them.

Reply | Quote

An example file of how you can achieve your result is attached. Its is in Excel 2003.

If you look at the code I have written, you will see that I had to make all your names UPPERCASE as you have a mixture in your two tables.

Reply | Quote

Hi

I do not quite follow the meaning of 2316.38 as Martin pointed out. Are you trying to return all the value per the lookup! See the attached, might be of some help!

Reply | Quote

Worksheet formulas can generally only put results into single cells.

It is possible to get the multiple cells populated – as Kevin has illustrated.

“I don’t use them“. May I suggest that it might be easier to learn how to write a simple Macro than to go for the intensely complex formulas needed to do the job ? I taught myself, mostly by recording Macros and seeing what they did, then polished them up with much help from this Lounge. Just a thought.

Reply | Quote

@MartinM, It’s a great suggestion! I’ve always avoided, will give it a try. Thanks.

Kevin@Radstock. What you provided is perfect! The results are as I desired. I attempted to copy into my original spreadsheet and change the range, but it won’t work? Any ideas why it’s not working?

Reply | Quote

Hi ab2537

I forgot to add to post #7 that it is an Array formula. You need to CTRL + SHIFT + ENTER to enter the formula, not just enter, then copy across and down.

Kevin

Reply | Quote

Thanks, Kevin.

Reply | Quote