Excel help please.

Colbaker · 9 Feb 2013 at 13:43

Hi all,

What equation would i use in Excel for this...

I want to enter some data into Cell A1 (for arguments sake) but then i want Cells A2, A3, A4, A5, A6 to be populated by the information relating to the data entered into Cell A1,

For instance, I want to add someones name into A1 (arguments sake TED), I then want the equation to search the data i have added to book 2 of the Excel spreadsheet and populate Cells A2, A3, A4, A5 and A6 with TED's Address, Phone number, Age, Date Of Birth and height.

What would the equation be?

Antar Bolaeisk · 9 Feb 2013 at 14:10

=match() will get you the row number the name is on.
=Indirect() will let you dynamically reference a cell using that number.

Colbaker · 9 Feb 2013 at 15:57

Thanks for the reply,

I am currently trying some different equations, This is the one i have used to do what i wanted...

=VLOOKUP($A$2,TABLE,COLUMN(B2),FALSE)

Now this works and i understand mostly why, what i dont understand is what the "COLUMN(B2) bit means, I can change the B2 bit to B5 or A6 and it doesn't seem to effect the results.

What does it actually mean, in layman's terms?

PMKeates · 9 Feb 2013 at 17:36

COLUMN() returns the column number of a reference. COLUMN(B2) = 2 (B is the 2nd column), COLUMN(A6) = 1 (A is the first column).

The third value in a VLOOKUP formula is the column of TABLE that it should return data from. If this third value is COLUMN(B2) which = 2, it will return data from the 2nd column. If you just put the number 2 instead of COLUMN(B2) it will have the same effect.