• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to return values across 4 columns based on employee cost centre number

S

Stephen

New Member
I need to report all employee's in a cost centre, and list them with some data:


Cost centre 100

Joe bloggs sales Sydney

Fred Dagg admin Melbourne

Croc Dundee Animals Darwin


So the idea is in one cell we enter the cost centre number in to cell ~A1, then a VLookup function returns all folks in the list, then extends across to pick up 4 other columns.

Straight Vlookup brings back first value and not any others (there can be 1 or 30 employee's). My brain is mash at this point and not working. Someone give me tips that I probably will remember as soon as you write them?
 
Hi, Stephen!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords "vlookup multiple values" and press Search button. You'd retrieve many links from this website, like the following one(s), maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


http://chandoo.org/forums/topic/vlookup-to-return-multiple-corresponding-values

http://chandoo.org/forums/topic/returning-multiple-values-with-vlookup-array


Do you think this could help?


Regards!
 
Hi SirJB7


Good to see the forum police are out and about; every forum needs one. The standard reply could be warmer dude :)


I did actually try searching for what I needed, and your two suggstions are included. The first one links to a Microsoft result that has faulty formulas. Second one is close but deals with numerals, not text and me not being a smart man can't translate the pure formula to reality.


I am hoping to return non-numerical values in my lookup. I am compiling a report that will search for staff and equipment based on a common criteria; in this case, a cost centre. I have already used the SUMPRODUCT formula to return actual values in the list, so this is all generated off the cost centre in one cell, which then updates the graphs I have listed. We use around 100 cost centres and this means 1 template for all offices, they simply enter their cost centre number and the graphs update (BTW, I love that post, it was exactly what I needed). The intention is to link this formula into that cell with the cost centre number then like sumproduct/vlookup refresh the list of items specific to that cost centre number.


The data is listed thus:

1060484 136MVC Commodore Wagon Fred Bloggs 1/08/2012 100,947

1060429 1DAS489 Commodore Wagon Barney Rubble 29/10/2012 93,452

1060421 781LQT Rav4 Wagon Fred Flinstone 15/11/2012 59,831

1060482 BA10SA Rav4 Wagon Wilma Flintstone1/11/2012 78,490

1060485 WQM003 Commodore Wagon Betty Rubble 3/08/2012 88,079

1652802 BI39MB Falcon Wagon Dino Flintstone 16/03/2015 74,970

1652802 BK64KD Commodore Wagon BamBam Rubble 30/03/2015 52,761


So what I want to do is to search Column A, looking for the common cost centre number. In this example I have 2 people in c/c 1652802 and I want to present the data across the line in a new spreadsheet report. Copy and past is no good, as above we generate info based on the cost centre being entered into cell A.


So my drama is I don't want to sum numbers or anything to do with numbers; I simply want to return values as above based on a identifier.


Sorry, I am not that expert on excel and this is a bit beyond me. I don't write formula's off the top of my head, I still use the lkittle box to do it. Hopefully there is an example that can be posted that shows me how it is done; once I see it in action I will be able to work on it.


My sheet holding the graphs for analysis are on another sheet to the raw data, so in effect I am trying to do a dashboard at a lkevel i can cope with, before trying to attempt a dashboard.
 
Hi, Stephen!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Need%20to%20return%20values%20across%204%20columns%20based%20on%20employee%20cost%20centre%20number%20%28for%20Stephen%20at%20chandoo.org%29.xlsx


Added a helper auxiliary column (I) in sheet Hoja1, and in sheet Hoja2 see the yellow shaded cells:

A2 : Cost Center (input)

B2:H6 : {=SI.ERROR(INDICE(Hoja1!$A$2:$H$8;COINCIDIR($A$2&FILA()-1;Hoja1!$A$2:$A$8&Hoja1!$I$2:$I$8;0);COLUMNA());"")} -----> in english: {=IFERROR(INDEX(Sheet1!$A$2:$H$8,MATCH($A$2&ROW()-1,Sheet1!$A$2:$A$8&Sheet1!$I$2:$I$8,0),COLUMN()),"")}


It's an array formula so it should be entered with CtrlShift-Enter instead of Enter.


Just advise if any issue.


Regards!
 
Hi JB


That is exactly what I need and WAAAAAAYYY over my abilities.


Just one more dumass question; CtrlShift-Enter? Is there a trick? I hold ctrl&shift then press enter.


Nothing doing.


We are using office 10 if that matters
 
Hi, Stephen!

Array formulas are inputted pressing simultaneously Ctrl Shift and Enter keys (the three). But only if you're editing or writing it, otherwise can be copied as usual, if they don't replace another array formula, then it's safer to clear range first.

If entered an array formula as a normal formula, edit with F2 and then Ctrl-Shift-Enter.

Regards!
 
Help! Anyone? Even if I try writing the formula manually it doesn't work.


ctrl-shift-enter does zip all if I try to copy the formula off the sheet or the website
 
Hi, Stephen!

Could you please upload your file? Check second green sticky post at this forums main page for guidelines.

Regards!
 
OK, seems to be not liking my data range - in my spreadsheet Hoja1!$A$2:$H$8 is replaced by Data!$Q$2:$x$769 (which is the range I am looking at but it keeps highlighting the $Y$769 when I check it.
 
Hi, Stephen!


Hoja1!$A$2:$H$8 is changed by Data!$Q$2:$X$769.

Hoja1!$A$2:$A$8 is changed by Data!$Q$2:$Q$769 too?

Hoja1!$I$2:$I$8 is changed by Data!$Y$2:$Y$769 too?


Check out that, please.


Regards!


EDIT: You should adjust ROW() to ROW() +/- NN, where NN is the difference between column number of Q in your Data sheet and Plate? output column in your target sheet (NN=16 if output column is B).
 
yes, all changed as your post


I am correct in assuming; all data being read is the range I want and the last piece is checking the helper column?
 
Hi, Stephen!

Can't change to dummy data as explained in related post?

About the EDIT, in my uploaded file source data is in columns A:H + helper I, and target data is in columns A:H (A input, B:H formulas). So data retrieved from a column is stored in same column number (COLUMN()). But if your actual data ranges from Q:X + helper Y and your output is still in columns A:H, you should indicate the offset between R:X and B:H columns (retrieved range, outputted range): R-B=18-2=16.

Got it?

Regards!
 
Thanks Sir. I have managed to make a success of the sheet I was working on. Appreciate your assistance.
 
Hi, Stephen!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top