Aligning rows. Help...

[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.

 

[Jacob Skaria]

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