Cell Referencing

**TechTed** · 12-23-2011, 11:00 AM

I'm making a spreadsheet for work where you first copy + paste a list of current investments and balances, and copy + paste a list of all available investments, to the spreadsheet. Right now the calculations just run straight across the row, pulling information from carious cells in that row. Ideally the information would copy as below.

InvestmentA Calc Calc Calc Calc InvestmentA Calc Calc Calc
InvestmentB Calc Calc Calc Calc InvestmentB Calc Calc Calc
InvestmentC Calc Calc Calc Calc InvestmentC Calc Calc Calc

But sometimes it doesn't, and I get something like:

InvestmentB Calc Calc Calc Calc InvestmentA Calc Calc Calc
InvestmentH Calc Calc Calc Calc InvestmentB Calc Calc Calc
InvestmentM Calc Calc Calc Calc InvestmentC Calc Calc Calc

What I need is a way to have the calculations on the first half of the spreadsheet, reference information in the cells with the same investment name, even if that information is in a different row.

Disclaimer: I consider myself an intelligent person, but I have no official education in programming or excel, except for one class in 9th grade. I'm teaching myself as I go here. In other words, it's best to assume I know nothing and teach me like a 2 year old.

**JBeaucaire** · 12-23-2011, 11:07 AM

You can use the VLOOKUP() function to spot the correct row and retrieve values from the adjacent cells.

**TechTed** · 12-23-2011, 12:34 PM

Originally Posted by JBeaucaire

You can use the VLOOKUP() function to spot the correct row and retrieve values from the adjacent cells.

VLOOKUP(BTC).xls

VLOOKUP() is over my head. I've never used table arrays. I'm not sure my spreadsheet is set up in a way that VLOOKUP() can work. I think I could get it if I was able to see how it would need to be coded. I've attached the file I'm working on. If anyone has the time to take a look I'd appreciate it. Basically I need the calculations in columns B - F pulling information from columns J - N in the rows where A and H are equal.

**scottr1** · 12-23-2011, 05:51 PM

I'm not 100% sure what you're trying to achieve on the xls file. If you open this attachment you can find an example of a vlookup formula which should get you what you need.