Want to pull value from one sheet to another in matrix with multiple values

[joliver]

I have attached a sample file that shows what type of data I am working with.

I am looking for a way to pull the POSITION values (in column C) from the DATA sheet over to the MATRIX sheet and have POSITION value display in the correct cell based on the ROW (Level) and COLUMN (Function) values from the DATA sheet.

I have 500 data lines to work with and want to have the Matrix show lists of multiple positions that correspond with each Function and Level. Some cells will have multiple positons which have the same Function and Level combination

Can anyone let me know a good way to display this and which formulas to use?

[noboffinme]

Hi joliver,

A VLOOKUP will do it.

In order to have the Formula accept 2 values, I've added the 2 values together with an '&' & have the VLOOKUP look for that.

The grey shaded Columns have the values I've created for the VLOOKUP & should be hidden once you've had a look at them to see how this works.

So the values are matched by the Formula & added to the MATRIX sheet.

Let me know if there's anything else.

[joliver]

Hello noboffinme,

This is great. But what if there are multiple values for one combination? For example in my data there are two values for HR 56 and Finance 60. Is it possible to pull multiple values into the one cell in the matrix using the vlookup? Having it pull any POSITION value that has the same FUNCTION and LEVEL values?

[joliver]

I found this link today that has a VBA macro that when added to the spreadsheet allows me to run =multivlookup formula to pull multiple values in to one cell.

http://bals-brain.com/Multi_vlookup.htm

However it currently puts " , " in between each value that it returns in the cell.

Does anyone know how to alter the VBA coding so that it will put the next value in the same cell but below (ie: Alt+enter)

[joliver]

Currently the values show like this in one cell:

jjjjjjjjjjj, kkkkkkkkkkkk, mmmmmmmm, ooooooooo

I would like them to show like this in one cell:

jjjjjjjjjj
kkkkkk
mmmm
ooooooo

Does anyone have any suggestions? I know the VBA macro if I edit the ", " it will put a different symbol inbetween the values but can't figure out how to make it go down one line. Any suggestions?

Please Login or Register  to view this content.