Aligning rows. Help...

  • Thread starter Thread starter Anders
  • Start date Start date
A

Anders

My scenario:

I have Company Profit and Loss with 200 Rows of account number and names in
A1-200 with actual and plan figures in B and C 1-200.

Whilst in E-G 1-150 I have a P&L with account number/names and actual and
plan figures for a profit stream. But the company account number/names dont
match up on the same row as for the total company as not as many accounts has
been used.

I was wondering if there are any method of aligning the accounts and
connecting figures for the profit stream with the rows in the company P&L.
There would ofcourse be 50 blank rows on the P&L for the profit stream, but
all the Rows would match up so for example the TOTAL would be on row 200 for
both the company and the profit stream.
 
Use the formula VLOOKUP() .VLOOKUP stands for vertical lookup and search for
a value in the first column of a table array and returns a value in the same
row from another column in the table array. Try the below test with a similar
data and try changing the name in Cell C1

ColA ColB ColC ColD ColE ColF
Name Age Sex Adam =formula1 =formula2
Adam 32 M
Marie 24 F
Francis 33 M
Joel 34 M
Susan 26 F
- - -
- - -
- - -
- - -


in place of formula1
=VLOOKUP($D$2,$A$2:$C$10,2,FALSE)

in place of formula2
=VLOOKUP($D$2,$A$2:$C$10,3,FALSE)

For your data you can insert few cols after ColC and try the formulas from
ColD..and refer the array as the shifted E:G data with 150 rows...

If this post helps click Yes
 
Back
Top