how to import multiple values w/ a vlookup into a single cell

**zazzz** · 11-01-2010, 12:54 AM

ive posted this question on another forum as well but im not really getting a very informative answer there so wanted to ask it here. hope i can get some help... need this for a job interview

previous post

in my data worksheet there was Labour Expense 1 and Labour Expense 2.
in the main sheet I was dealing with I just had Labour Expense. how would i get the sum of both LE1 and LE2 in there? now of course if i did it one by one this would be no problem but we want this to be in a way that can be duplicated easily, so by using vlookups or sumif statements. b/c in the data sheet there was also raw material expense 1 and RM expense 2, then rent 1 and rent 2, etc. now in this case there was only 1 and 2, but what if there had been a third, is there any simple way of dealing w/ that as well? i guess the simple way of doing this was to say - if in this range (ie column A) there's a value which starts off with "Labour" I want the corresponding figure (in column B) to be added.

also in the data tab i had values for north, south, and east, but not west. when i created a vlookup to import those values in, west gave n/a instead of 0. which woulda been OK except the main worksheet then tried to calculate a total and average, both of which got messed up b/c of the n/a. what was the right way to tackle this?

**teylyn** · 11-01-2010, 03:39 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**zazzz** · 11-01-2010, 11:36 PM

OK I've put together a sample file with the problem. For simplicity rather than use 2 different sheets I changed 'data tab' to 'data section'.

So now B2,3,9,10,11 are wrong / not what im looking for. obviously one can manually fix this but with hundreds of lines thats not practical.

id appreciate the input.

**zazzz** · 11-06-2010, 01:36 AM

hello, anyone?

**JBeaucaire** · 11-06-2010, 03:14 AM

By moving the data into separate columns and shortening the strings in column A to just the strings needed to match to the variable data strings in the data section, simple SUMIF() formulas can do all the work.

=SUMIF(F:F, A2 & "*", G:G)

**zazzz** · 11-07-2010, 12:17 PM

that's awesome, thanks!

what if we cant change the data around? in this case it was easy b/c there were only a few lines but the test given was to test our abilities. he said there'd be thousands of lines. so if the data was to stay in the same columns how would it work? (in the original example in fact the data was on a separate page).

**JBeaucaire** · 11-07-2010, 04:38 PM

If a teacher gives you a question and the data layout is improper, I would point that out along with the correct solution, including the correction to the data layout for simplification.

Meanwhile, you know you can replace a whole column reference "A:A" with a specific range, right? "A1:A100"

**martindwilson** · 11-07-2010, 04:38 PM

test your abilities? what's that mean?
oh i know
" let's google the answer"

**zazzz** · 11-08-2010, 10:14 AM

not exactly a teacher, more like a job interview. over there the flexibility is a much less

and the point of this exercise when they gave it was not to see if i can solve a question involving 10 lines of data - i could have even copy/pasted the solution for that - but to see what i'd do with thousands of lines.