[Help] Excel help...

matt

Well-known member

Mar 19, 2007

1,539

• Apr 8, 2021

• #1

Hi Spreadsheet Stars.

Ths is probably really easy but I don't know the correct terminology to Google it....

Column A contains names of people, e.g. A1 = John Smith, A2 = Bill Jones
Col B contains the result of a formula that produces an integer, e.g B1 = 2, B2 = 38 (there's no relationship between Col A and Col B)
I would like to use the integer from Col B to show the value of Col A in Col C.

For example if B15 = 54, in cell C15 I would like to display the the contents of A54

Thanks in advance for any help

 

Dorset Seagull

Once Dolphin, Now Seagull

Feb 21, 2009

4,426

Shaftesbury Dorset ( Hovis hill to you )

• Apr 8, 2021

• #2

matt said:

Hi Spreadsheet Stars.

Ths is probably really easy but I don't know the correct terminology to Google it....

Column A contains names of people, e.g. A1 = John Smith, A2 = Bill Jones
Col B contains the result of a formula that produces an integer, e.g B1 = 2, B2 = 38 (there's no relationship between Col A and Col B)
I would like to use the integer from Col B to show the value of Col A in Col C.

For example if B15 = 54, in cell C15 I would like to display the the contents of A54

Thanks in advance for any help

Click to expand...

If I understand correctly it sounds like you need to use the Vlookup function

 

Dorset Seagull

Once Dolphin, Now Seagull

Feb 21, 2009

4,426

Shaftesbury Dorset ( Hovis hill to you )

• Apr 8, 2021

• #3

Dorset Seagull said:

If I understand correctly it sounds like you need to use the Vlookup function

Click to expand...

In which case you we need a new 1st column with the row number in eg. A1 would need 1, A2 would need 2 etc. Not sure it would work as you integer is a formula not a number unless you copied and pasted down the column with the resulting integers to overwrite the formulas

 

schmunk

"Members"

Jan 19, 2018

9,496

Mid mid mid Sussex

• Apr 8, 2021

• #4

The easiest way is to have Column A as numbers 1-X, Column B with the names, Column C with the generated integers and then in D1 have =INDEX(A:B,MATCH(C1,A:A),2) and copy this down.

Index Match is a bit more robust than a Vlookup, as it will automatically change if you subsequently add in columns.

It is possible without the column of numbers in A, but more complicated.

 

S

seagull_si

Active member

Jul 8, 2011

213

Peacehaven

• Apr 8, 2021

• #5

=IF(B17=54,A17,"")

this was done on row 17 not 1

 

clapham_gull

Legacy Fan

Aug 20, 2003

25,309

• Apr 8, 2021

• #6

As above flip the columns round, so the number is COL A and the names in COL B

You can then use VLOOKUP

Or (as I tell everyone these days) dump formulas completely and use Power Query.

 

sydney

tinky ****in winky

Jul 11, 2003

17,750

town full of eejits

• Apr 8, 2021

• #7

have a fag...!