Vlookup from another work sheet and sum values along a row

[dabrad89]

Hi and thanks for the help on my last thread here's my issue

I am trying to vlookup to another worksheet and sum the results from a set number of coloumns heres what I have so far

=SUM(VLOOKUP(A1989,'other work sheet'!$A$1:$IV$65536,{73,74,75},0))

This formula is only returning the value from coloumn "73" ? Help !!!

[pooja_deshpande]

its an array formula.
select the formula cell, press F2, then press Ctrl+Shift+Enter

[dabrad89]

WOW im an idiot .. i knew it ws an array i was using the wrong commands haha
THANKS!!!

its an array formula.
select the formula cell, press F2, then press Ctrl+Shift+Enter

[dabrad89]

I also have 1 other problem
Can you vlookup to another sheet and then drag the formula sideways the return the next value in the row keeping the same reference cell
This is what I've tried to no avail

=vlookup($a$1,'Other Worksheet'!$A1:$Z99$999,25,false)

[Kevin UK]

This is a duplicate thread!

As TMShucks gave you the solution earlier today!

http://www.excelforum.com/excel-form...ong-a-row.html

[dabrad89]

ok after i posted the 1st one ... IE crashed so i did it again as i didnt think it went through .. thanks though ... any thoughts on my second query?

This is a duplicate thread!

As TMShucks gave you the solution earlier today!

http://www.excelforum.com/excel-form...ong-a-row.html

[pooja_deshpande]

sorry, but I am unable to get your second question.
can you elaborate a bit more?

[dabrad89]

I have figures from last year broken down by month in consecutive coloumns these are sat on one spread sheet i want to vlookup these figures into another sheet .. this is simple just a normal vlookup ... however copying the formula across 12 cells(12months) and editing the formula .. i.e. coloumn 1-12 .. is labourious as i update this sheet everyweek i awnt to be able to drag the formula across the row without having to edit it

[Kevin UK]

Hi

Do you mean cell reference!

http://office.microsoft.com/en-us/ex...010342940.aspx

[pooja_deshpande]

i think you need an hlookup instead of vlookup. The parameters are the same, except at the end, instead of column number, you have to specify the row.

e.g. you data is like this

Jan Feb Mar Apr
10 20 30 40

and you want

Jan HLOOKUP(J9,$J$4:$M$5,2,FALSE)
Feb 20
Mar 30
Apr 40

$J$4:$M$5 is your source reference in the other sheet.

[dabrad89]

My data is like this on both work sheets

acc ID jan feb mar apr may

123 £20 £30 £40 £50 £60
456 £20 £30 £60 £50 £60
879 £30 £40 £50 £60 £70

I want to vlookup the figure for jan on account number 123 and then dray the formula across for feb and mar ect