Help Needed in Writing a Query 1

[fergmj]

I have placed a link to a spreadsheet


In my everyday life, the number of rows changes so I am looking for a query that will return the row number when A1:A100="Enhancement Total"

So in this case, I was thinking that when I have the row number (in this case A24), then I would go to the E column in the same row (E24) and populate G58 with the number from E24.

The problem is that the number of rows varies so I can't always rely on this being E24. It may be E19 or E68. I need to know the row by looking for the "Enhancement Total" in column A.

Thanks.

fergmj

 

[SkipVought]

Hi,

You can use VLOOKUP, to find the TOTAL row and eturn the value in colunm E (column 5)

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[fergmj]

How can I get VLOOKUP to return a total? I can get it to return "Enhancement Total" but not a row number.

 

[SkipVought]

Use the MATCH function to return offset rows.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[fergmj]

Ok so now I have the number (THANK YOU VERY MUCH!!!)

=MATCH("Enhancement Total", D1100, 0)

returns 24

Now lets say in cell J60, I want to first run the above query to get the row number and then use it to get the value of the cell in column H for that row and copy that value to my J60 cell?

Meaning if H24 is 175.00, how can I return the number 24, go to the H column and retrieve the value in that column for the returned row (H24) and copy is to J60?

 

[SkipVought]

Please do not use the term "query." This is a FORMULA. A query means something completely different.
[tt]
=INDEX(H1:H100,MATCH("Enhancement Total", D1100, 0),1)
[/tt]




Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[AnotherHiggins]

FYI: You keep saying Query when I'm pretty sure you mean FUNCTION or FORMULA. The word "QUERY" is already 'taken' and refers to something quite different.

To answer your question: Wrap the MATCH function inside an INDEX function.

Like this:

=Index(H1:H100,MATCH("Enhancement Total", D1100, 0))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

[gray]...and don't press submit after walking away from your desk unless you first first check to see if "quick draw Skippy" has already answered[/gray]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Do I hear an echo in here?
_{Do I hear an echo in here?}
[gray]_{Do I hear an echo in here?}[/gray]


Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}