Look up cells in ranges

Hi mjpalma,

If you know the web site then you could use a VLOOKUP

Cheers

Dave

sorry, I started posting to this one and then I stopped part way through when I saw Brad's answer. I must have hit submit when closing the window

My first suggestion was VLOOKUP which was included

The second part which I hadn't added was using a find routine, my example used SUMPRODUCT to avoid using an array formula
=MAX(IF(NOT(ISERROR(SEARCH("web site",A1:A10))),ROWS(1:10),0))
which assumes there is only 1 valid match to be found

but Shane's wildcard MATCH is a better approach

Cheers

Dave

hmmm...bad copy and paste. This was my aborted SUMPRODUCT to work on A1:A10

=SUMPRODUCT(--(NOT(ISERROR(FIND("web site",A1:A10)))),ROW(1:10))

exiting question :)

Cheers

Dave

Hi again,

If you just want the locatin of the item use only the portion of my suggestion that reads:

 MATCH("Web Site:*",A1:A16,0)

If you just want to return the contents of that entry then use just this part:

OFFSET(A1,MATCH("Web Site:*",A1:A16,0)-1,0)

But if you want to return the URL from the cell without have separate formulas then use the whole thing, it saves you doing a MID function although you could do that also:

=SUBSTITUTE(OFFSET(A1,MATCH("Web Site:*",A1:A16,0)-1,0),"Web Site: ","")

=MID(OFFSET(A1,MATCH("Web Site:*",A1:A16,0)-1,0),11,200)

The "11" argument will depend on how long the leading text in front of the URL is.  The "200" is just a large enough number to catch any length url.

Shane Devenshire

All, thanks for the fast responses, I used Shane's
=SUBSTITUTE(OFFSET(A1,MATCH("Web Site:*",A1:A16,0)-1,0),"Web Site: ","") solution, and it worked perfectly.

For the second problem let me rephrase it, since there was some confusion:

where ? = row number,
Where ? can vary from row 7 to 12 so far (it may vary in a wider range but I have not seen that yet)

Starting in cell A?- there is a table of 7 rows and 9 columns that I am trying to retrieve
Here is an example, "Company" is located in cell A?

A?                     B?           C?         D?         E?             F?            G?             H?                     I?
Company                        Symbol      Price      Change      Market Cap        P/E
Automatic Data Processing Inc            ADP      44.75      1.40%      26.07B              28.11
Accenture Ltd                  ACN      25.13      1.99%      23.29B              20.65
Paychex Inc                  PAYX      33.31      1.65%      12.60B              40.72
Moody's Corp                  MCO      79.8      0.03%      11.79B              32.16
Reuters Group PLC                  RTRSY      41.19      1.01%      9.83B              15.95
Gartner Group Inc                  IT      11.69      0.09%      1.57B              83.5

Columns b,c,d are empty

the header row, starting with "Company" is always present, so if I can use that row for a reference point, what I need is a formula that searches for the cell with the header text and then reurns the approriate cell below the reference cell.

HLookup did not work because the row numbers may change, the same with offset so far.  I tried nexting both within an index and a match function formula, neither worked.

Again thanks for the quick response and I am grateful for the help

So out of the sample data which item do you want to find?  Please give us a specific example of the desired return value using the sample data you posted.

For example I want to find Moody's Corp's P/E  ----> 32.16

Let's assume that is what you want to do, then enter the Company you want in cell A1 and the Item you want in cell B1, in C1 enter the formula:

=OFFSET(A6,MATCH(A1,A7:A12,0),MATCH(B1,B6:I6,0))

Shane Devenshire

Sure, an example is I want to find the third company name in the list.  I know that "Company" will always be returned by the query, the list of companies will change based on which web page I query. So, in this case, Paychex Inc is the third company.  If I query a different web page, the third company on this list may be Ford.  So I cannot just look for ford.  

At the same time, the header row will change, so I need a formula that will find the right cells anywhere on the work sheet.  I tried your formula above and it did not work, I think because there it needs a definite starting point.

So what I need for the formula to do is search column A for "Company", and then return the name of the third company on the list, "Paychex Inc." in this case, located 3 rows below which ever row "Company" is located.

thanks

Jim, I am having problems with your formula, its returning a value from further down column A than Paychex.  Specifically, "Company" is currently located in row 36 (keeping in mind this will change depending on the website being queried), the formula is returning the value for row 78.  Could the problem be related to the reference value "A1"?  

Thanks for your help and patience
Mike

Brad, It worked perfectly, thanks to all of you
Mike

Mike,
In a multi-part question with input from a number of experts, it is common to split the points among several different experts. If you look at the top of the Comment box, there is a link for "Split Points". If you wouldn't mind, I am going to reopen the question so you can try this feature out.
byundt--Excel TA Page Editor