Vlookup to Return Cell Name Not Value?

[it]

I'm wondering if I can do the following with this table...on
tab1(snippet of raw data)
colA colB
2 55
3 57
6 51
9 40
10 42

I was trying to get vlookup to to work on tab2...
colA colB
3 * this is where I want to use vlookup
9

What I need to do is sum up all values in tab1.colB from 3 through 9.
So I was thinking if I could get vlookup to return the cell number
then I can do a sum from the return value. I hope I'm making this
clear. Any help is appreciated.

-Bruce

 

[Mike H]

If I've understood correctly this may do what you want:-

=SUMIF(Sheet1!A1:A5,">=3",Sheet1!B1:B5)-(SUMIF(Sheet1!A1:A5,">9",Sheet1!B1:B5))

Mike

 

[Joel]

set sumrange = sheets("Tab1").Range("B3:B9")
mysum = worksheetfunction.sum(sumrange)

 

[Niek Otten]

Hi Bruce,

Use MATCH() instead of VLOOKUP. MATCH() returns the relative position of the item found

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I'm wondering if I can do the following with this table...on
| tab1(snippet of raw data)
| colA colB
| 2 55
| 3 57
| 6 51
| 9 40
| 10 42
|
| I was trying to get vlookup to to work on tab2...
| colA colB
| 3 * this is where I want to use vlookup
| 9
|
| What I need to do is sum up all values in tab1.colB from 3 through 9.
| So I was thinking if I could get vlookup to return the cell number
| then I can do a sum from the return value. I hope I'm making this
| clear. Any help is appreciated.
|
| -Bruce
|

 

[it]

I did use Match (thanks Niek). Here's what I got:

=ADDRESS(MATCH($C28,Charges!$C$1:$C$401),
8,,,"Charges")&":"&ADDRESS(MATCH($C40,Charges!$C$1:$C
$401)-1,8,,,"Charges")

Which returns..exactly correct!
Charges!$H$40:Charges!$H$52

But now the problem is that I need to sum up this range and if I just
add SUM() it won't work because it's being treated as a string...help?

-Bruce

 

[it]

I got it working with this...thanks for the replies!

=SUM(INDIRECT("Charges!"&ADDRESS(MATCH($C136,Charges!$C$1:$C$401),
8)&":"&ADDRESS(MATCH($C148,Charges!$C$1:$C$401)-1,8)))/2000

-Bruce