Help??? A simple formula

silentcoates

New Member
Joined
Oct 11, 2005
Messages
41
Hello

I was wondering if you can help. I just cannot remember for the life of me how to do something.

I have two columns of data A has names in it and B has numbers. In column F I have a shorter list of names (all of which are contained with column A). I need a formula to put in column G next to the names in column F to display the number in column B next to the correct name.

Hope this makes sense.

thanks for any help offered
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,803
Office Version
  1. 365
Platform
  1. Windows
Hi,

If there's only one occurence of the name in column A then in e.g. G1 formula is: =VLOOKUP(F1,A:B,2,0) and copy down to end of range

of if there are multiple occurences and you want the total of the name then formula is: =SUMIF(A:A,F1,B:B)

Adapt ranges to suite...

Regards,
Jon
 

Tyler_Dirton

Board Regular
Joined
Oct 24, 2006
Messages
50
Hi,

If I understand you correctly, you need to use a VLOOKUP formula to populate column G. eg

If your list of names ranged B2:B200, the cell in G2 would read

=VLOOKUP(F2,A2:B200,2,TRUE)

where F2 is the start of your shorter list of names, A2:B200 is the list of full names with numbers against each, 2 for the column of values you want to return.

Hope this helps.

TD
 

Watch MrExcel Video

Forum statistics

Threads
1,114,059
Messages
5,545,759
Members
410,704
Latest member
Cobber2008
Top