vlookup or index ??
 

I am a novice with these functions. I have a spreadsheet full of various a/p
invoice information for hundreds of utility accounts. From the one
spreadsheet I need to create around 50 with invoice info separated by account
# and by month. I don't think a vlookup works because I need to return
multiple responses for one lookup and the # of responses can vary. I tried
the following index function which sort of works
=INDEX('Utility Report'!$A$1:$J$18062,SMALL(IF('Utility
Report'!$C$1:$C$18062=$K$17,ROW('Utility Report'!$C$1:$C$18062)),
ROW('Utility Report'!$1:$1)),4)
the problem I am having is it isn't returning the info from the line I am
looking for and I don't really understand the components of this function.
This is returning info from the exact row number on the "utility report"
spreadsheet as the formula is entered in on the new tab. So if I have the
formula in row 9 it is returning the info from "utility report" row 9. I am
looking for it to return the info only from the rows where column C = the
data in cell K17. Which in this case happens to be in rows 5802-5813.

vlookup or index ??
 

Hi

Wouldn't Sumproduct give you the result?
=SUMPRODUCT(--('Utility Report'!$C$1:$C$18062=$K$17),
'Utility Report'!$D$1:$D$18062)

--
Regards
Roger Govier

"eec" wrote in message
...
I am a novice with these functions. I have a spreadsheet full of various
a/p
invoice information for hundreds of utility accounts. From the one
spreadsheet I need to create around 50 with invoice info separated by
account
# and by month. I don't think a vlookup works because I need to return
multiple responses for one lookup and the # of responses can vary. I
tried
the following index function which sort of works
=INDEX('Utility Report'!$A$1:$J$18062,SMALL(IF('Utility
Report'!$C$1:$C$18062=$K$17,ROW('Utility Report'!$C$1:$C$18062)),
ROW('Utility Report'!$1:$1)),4)
the problem I am having is it isn't returning the info from the line I am
looking for and I don't really understand the components of this function.
This is returning info from the exact row number on the "utility report"
spreadsheet as the formula is entered in on the new tab. So if I have the
formula in row 9 it is returning the info from "utility report" row 9. I
am
looking for it to return the info only from the rows where column C = the
data in cell K17. Which in this case happens to be in rows 5802-5813.

vlookup or index ??
 

Thank you, but I am not trying to sum anything. I am trying to find all
invoice information for a certain account #. So my account # is in cell K17
on my "new" spreadsheet and I want to find all of the matches in column C of
my "Utility Report" spreadsheet and extract that info on to my "new"
spreadsheet.

"Roger Govier" wrote:

Hi

Wouldn't Sumproduct give you the result?
=SUMPRODUCT(--('Utility Report'!$C$1:$C$18062=$K$17),
'Utility Report'!$D$1:$D$18062)

--
Regards
Roger Govier

"eec" wrote in message
...
I am a novice with these functions. I have a spreadsheet full of various
a/p
invoice information for hundreds of utility accounts. From the one
spreadsheet I need to create around 50 with invoice info separated by
account
# and by month. I don't think a vlookup works because I need to return
multiple responses for one lookup and the # of responses can vary. I
tried
the following index function which sort of works
=INDEX('Utility Report'!$A$1:$J$18062,SMALL(IF('Utility
Report'!$C$1:$C$18062=$K$17,ROW('Utility Report'!$C$1:$C$18062)),
ROW('Utility Report'!$1:$1)),4)
the problem I am having is it isn't returning the info from the line I am
looking for and I don't really understand the components of this function.
This is returning info from the exact row number on the "utility report"
spreadsheet as the formula is entered in on the new tab. So if I have the
formula in row 9 it is returning the info from "utility report" row 9. I
am
looking for it to return the info only from the rows where column C = the
data in cell K17. Which in this case happens to be in rows 5802-5813.

vlookup or index ??
 

Hi

the try using advanced Filter.
you need to start the filter from the destination sheet.
Take a look here for more details
http://www.contextures.com/xladvfilter01.html#ExtractWs

--
Regards
Roger Govier

"eec" wrote in message
...
Thank you, but I am not trying to sum anything. I am trying to find all
invoice information for a certain account #. So my account # is in cell
K17
on my "new" spreadsheet and I want to find all of the matches in column C
of
my "Utility Report" spreadsheet and extract that info on to my "new"
spreadsheet.

"Roger Govier" wrote:

Hi

Wouldn't Sumproduct give you the result?
=SUMPRODUCT(--('Utility Report'!$C$1:$C$18062=$K$17),
'Utility Report'!$D$1:$D$18062)

--
Regards
Roger Govier

"eec" wrote in message
...
I am a novice with these functions. I have a spreadsheet full of
various
a/p
invoice information for hundreds of utility accounts. From the one
spreadsheet I need to create around 50 with invoice info separated by
account
# and by month. I don't think a vlookup works because I need to
return
multiple responses for one lookup and the # of responses can vary. I
tried
the following index function which sort of works
=INDEX('Utility Report'!$A$1:$J$18062,SMALL(IF('Utility
Report'!$C$1:$C$18062=$K$17,ROW('Utility Report'!$C$1:$C$18062)),
ROW('Utility Report'!$1:$1)),4)
the problem I am having is it isn't returning the info from the line I
am
looking for and I don't really understand the components of this
function.
This is returning info from the exact row number on the "utility
report"
spreadsheet as the formula is entered in on the new tab. So if I have
the
formula in row 9 it is returning the info from "utility report" row 9.
I
am
looking for it to return the info only from the rows where column C =
the
data in cell K17. Which in this case happens to be in rows 5802-5813.