VLOOKUP with multiple records

[silmiya]

Hello friends,

I want to find out the following. I have 2 sheets namely Master and Vlookup. Master sheet has all the records and Vlookup is the formula sheet.

I want to know how I can get information extracted to my Vlookup sheet when there are multiple records with same name but with different mobile number.

Refer highlighted cells.

[tim201110]

=AGGREGATE(15,6,ROW(Master!$A$2:$A$6)/(Master!$A$2:$A$6=$A2),COUNTIF($A$2:$A2,$A2))

[silmiya]

=AGGREGATE(15,6,ROW(Master!$A$2:$A$6)/(Master!$A$2:$A$6=$A2),COUNTIF($A$2:$A2,$A2))

Thank you for your help but it didn't work. I want this information to appear. If you look at my Master sheet, it has 2 records with Mifzal who is 40 years and has 2 mobile numbers.

I want to appear all records on Vlookup sheets when I enter "Mifzal".

Meaning, it should appear,

Mifzal 40 777123456 and
Mifzal 40 777654321

[ben_hensel]

Returning multiple matches (instead of exactly one match) is actually pretty far outside the scope of VLOOKUP. It's doable, but it becomes very dependent on table structures etc. which means it's easy to give you a formula you don't understand that limits what you get out.

So let's talk about non-VLOOKUP options.

What is the desired output you want here? Then we can think about achieving that, instead of hacking VLOOKUP particularly.

[silmiya]

Returning multiple matches (instead of exactly one match) is actually pretty far outside the scope of VLOOKUP. It's doable, but it becomes very dependent on table structures etc. which means it's easy to give you a formula you don't understand that limits what you get out.

So let's talk about non-VLOOKUP options.

What is the desired output you want here? Then we can think about achieving that, instead of hacking VLOOKUP particularly.

Ok Sir. Thank you. This is what I want. I have a subscriber database. I want to extract information when I put subscriber's ID number on cell A2. I want these fields to fill automatically.

Subscriber's name, Subscriber's mobile number, his age etc.

Remember, a subscriber can have more than 1 mobile number. So, I want to appear multiple records when we enter subscriber's ID number.

Example : Subscriber A will have 2 mobile numbers. Subscriber B will have 1 number. I want all 3 records to appear.

[silmiya]

Anyone out there? I am still waiting for help. Thank you.

[silmiya]

Do we have to pay for the service?

[JeteMc]

There are no ID numbers on the master sheet and cell A2 on the Vlookup sheet has the name Mifzal. It would help us to help you if the file matched up with the description.
That said, if you are going to keep the name of the subscriber in cells A2:A4 on the Vlookup sheet then put this formula in cell B2:

Please Login or Register  to view this content.

After entering the formula drag the fill handle over to cell C2 then, while B2:C2 are still selected, drag the fill handle down to cell C4.
This forum is manned by volunteers, so patience is a virtue.
You do not have to pay for the service on this forum. There is a pay forum (commercial services) on the site if you need faster help.
Let us know if you have any questions.

[m.jea]

Here is:
INDEX(Master!B$2:B$6,AGGREGATE(15,6,ROW(Master!$A$2:$A$6)-ROW($A$1)/(Master!$A$2:$A$6=$A2),COUNTIF($A$2:$A2,$A2)))
Drag down and to the right

[silmiya]

Here is:
INDEX(Master!B$2:B$6,AGGREGATE(15,6,ROW(Master!$A$2:$A$6)-ROW($A$1)/(Master!$A$2:$A$6=$A2),COUNTIF($A$2:$A2,$A2)))
Drag down and to the right

Thank you very much and it worked. I appreciate your help

[silmiya]

There are no ID numbers on the master sheet and cell A2 on the Vlookup sheet has the name Mifzal. It would help us to help you if the file matched up with the description.
That said, if you are going to keep the name of the subscriber in cells A2:A4 on the Vlookup sheet then put this formula in cell B2:

Please Login or Register  to view this content.

After entering the formula drag the fill handle over to cell C2 then, while B2:C2 are still selected, drag the fill handle down to cell C4.
This forum is manned by volunteers, so patience is a virtue.
You do not have to pay for the service on this forum. There is a pay forum (commercial services) on the site if you need faster help.
Let us know if you have any questions.

Thank you for trying to help me and I appreciate it. I am sorry for asking for assistance in a faster away since I was stuck. Sorry, again.

[silmiya]

I had to reply to this thread again because the previous solutions didn't work. I am putting an easy database for you to understand. Attached is a new file and it's named as "Policies". This file has 2 sheets, named "Master" and "Formula". Master sheet has all the information. I want to extract information to Formula sheet based on the following condition.

I have picked 2 policy numbers as examples.

1) If you look at policy # C11VT2DP04458/18 on the master sheet, it has only 1 record
2) If you look at policy # AG3VT2DP01190/18 on the master sheet, it has 4 records

I want to perform this. When I type a policy # on A2 of Formula sheet, B2 and C2 should be automatically filled. Remember, the same policy will have more than 1 record as shown above (refer item # 2).

When I type AG3VT2DP01190/18, it should fill B3 and C3 and it should keep filling A4, A5 & A6. This scenario can have information like this

Since AG3VT2DP01190/18 has 4 records, only the first cell (A3) can be filled. Based on that policy number, B3, B4 to be filled. Refer the Formula worksheet.

[JeteMc]

See if the following would be an acceptable work around.
Type the policy #'s that you want to examine into column A. (Actually I would suggest producing a unique list and link to that list with drop downs to avoid possible errors).
Column B is populated with the number of records for that policy number using: =COUNTIFS(Master!A$2:A$150,A2)
Column C is populated with beginning row numbers for the 'output table' using: =IF(A1="","",IF(ROW()=2,2,SUM(B1,C1)))
On the 'output table' (columns F:H)
The policy # column is populated using: =INDEX(A$2:A$10,MATCH(ROW(),C$2:C$10))
The receipt # and collected amount columns are populated using:

Please Login or Register  to view this content.

Let us know if you have any questions.

[silmiya]

Exactly. This is what I want Sir. But a small issue? Can't I get rid of the information from column A, B and C?

[AliGW]

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

[silmiya]

Ok Madam. I understand. Sorry about it.

[JeteMc]

The information in columns A:C is what produces the 'output table'. You could move the 'output table' to another sheet as modeled in the attached file.
Let us know if you have any questions.

[silmiya]

Thank you Sir. Appreciate your help!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.