Formula: Find text in range, return value

[gger]

Hi Guys,

I've got a web query from reuters (modified using vba to return financial ratios for a ticker within a specified cell) that is acting a bit wonky and i can't seem to fix it.

i enter a ticker in to a cell and press a button that runs a bunch of web queries. when i get to the query that returns financial ratios from reuters it will return the data but place it in different cells every time. i think this is because the data i 'checked-off' when i ran the query adds data when it is available, and leaves data out when it isn't available. so i guess another way of saying this is that instead of returning 20 rows of data every time, it returns 18 some times, and 20 others.

This is OK with me - what isn't OK is placing it in different cells every time. i want to be able to, on another sheet, link to these cells but because the query doesn't drop the data in the same cells my values for the other sheets are off every time i run the code.

Now, i'm trying to tackle this problem using a formula. this is what i have.

=IF(ISNUMBER(MATCH("P/E Ratio (TTM)",Data!AR1:BA149,0)),AX3, "-")

i 'think' it will work, but i have two concerns. my first problem is that because i am searching for text ("P/E Ratio (TTM)") that was returned through the web query, it will not locate the text.

my second problem is that when the text is found, i want the formula to return the cell directly next to the text. or two cells from the text, etc. currently it won't do that...

Any thoughts?

Thanks! gger

 

[tushiroda]

you could clean up the query to return comma delimited values to allow you to manage empty fields. This should also even out the data to be consistent each time.

if you want to stick with the way you're going about it, try the OFFSET function and feed in the result of the MATCH function.

 

[gger]

you could clean up the query to return comma delimited values to allow you to manage empty fields. This should also even out the data to be consistent each time.

if you want to stick with the way you're going about it, try the OFFSET function and feed in the result of the MATCH function.

How would i go about cleaning up the query? what are comma delimited values?

sorry, i'm fairly new to this...

 

[gger]

bump...

 

[gger]

bump...

can nobody help me out here?