Using vLookup based on results from a vLookup & returning an undetermined list

Hi,

I have three tabs; People; Technology; & Overview. In the Overview tab I have used a basic vLookup function to populate fields based on inserting a 'Unique Team Id'. This works and as well as populating several fields it returns a 'Y' or a 'N' in a list of seven functions depending if these have been mapped in the People tab.

My issue is that I then need to then list ALL technologies that are linked that also have a 'Y' for these seven functions (in the Technology tab) and return ALL technologies, i.e. it could be one but it could also be 100+.

In the attached spreadsheet on the 'Team Overiew' I have inserted 'Team_010' in the unique ID and the section with a background of green returns the right results from the '1_People' tab. Based on this example and 'Function One', 'Function three' & 'Function Five' all returning a 'Y' value I then need to somehow dynamically map ALL technologies that service these functions from the '2_Technology' tab.

I have highlighted in grey in the 'Team Overview' tab the results that should be returned based on the fields populated. Obviously I have sanitised the spreadsheet and reduced considerably the numbers so this is for a sample only but there would be a few hundred listings. If one technology supports a function, one technology should be returned. But if several technologies support one function, all mapped techno technologies should be returned.

I suspect that i need something other than vLookup to achieve this but I've hit a brick wall!

Hopefully I have placed this query in the right place and have given enough detail.

Thanks
NB

hi

please update your expected reult in your attachement

Hi Boopathiraha, thanks for the try but that doesn't seem to work. NB

Hi mohamedJamsheer,

I have included the five returns I am would like to get between B25:I29. I am starying to suspect that vLookup can not do this but I don't know enough to understand what other formulas can help.

NB

Sorry, just to clarify, the returns should be on the "Team Overview" tab.

NB

Please Login or Register  to view this content.

please check attachment...

thanks

Sorry attached

Hi Mohamed,

Thanks for the effort and I see what you have done and maybe that was a better way of doing what I already had achieved but on the 'Team Overview' tab, when I have 'Team_0010' in the Unique ID and it returns 'Y'(C14) for B14(1-Function One) I then want to auto populate the Technology systems from the '2_Technology' tab that have a 'Y' in the '1-Function One' column (H) on the '2_Technology' tab. In this particular case there should only be the details of 'Tech_001' returned. In the same scenerio for B16(3-Function Three) there should be 3 sytems details returned (Tech_0002, Tech_0005, Tech_0007).

Hope that makes more sense.

NB

hi Ban

i am confused now, can you please update your result in team view manually (expected result)

thanks

Hi Mohamed,

I have included an updated spreadsheet and I have put comments in outlining the Steps I need to take for the solution to work. It is Step 3 that I need help solving and getting returns from the '2_technology' tab based on 'y' responses.

Thanks
NB

How do you match c14 with system name, please explain...

You match the C14 cell by going to the 2_Technology tab.

In 2_Technology there are 8 Tech Systems listed. Once Step Two has done it's HLookup function and populated C14, and in the example given this is returned as a 'Y', I then want to look up all systems that have a 'Y' in the H column (1-Function One) of the 2_Technology tab, if any of these have a match to a 'Y' I want them to then list the System Name, System Description, System Number.

On the 2_technology tab there are:
1 system that meets Function 1 (H)
2 systems that meet Function 2 (I)
3 sytems that meet Function 3 (J)
0 systems that meet Function 4 (K)
2 systems that meet Function 5 (L)
0 systems that meet Function 19 (M)
1 system that meets Function 20 (N)

You will notice that when you change the unique Id from Team_0010 to Team_0004 the fields populated in cells C14:C20 will change. i need the solution to be adaptable to be able to pick this up.

Hope this makes the explanation easier.
Thanks
NB

please check attachment..

Not solved. Anybody else out there that can help?

Can anybody assist or let me know if what i am trying to do is not possible? tahnks NB