vlookup and cell address

pradhan · General User Joined: 01 Sep 2006 Posts: 48

How can i obtain the cell address of the cell found by using the function VLOOKUP ?

VLOOKUP returns a value that it finds in a table. I need to know the cell address too so that i can jump to it (like a cat).

Can anyone help ?

Thanks.

David · Posted: Mon Oct 30, 2006 6:55 am Post subject: Re: vlookup and cell address

Sorry, no answer here, but my curiosity [and lack of understanding] begs another question: Why? You will already now have the contents of that cell copied in the cell containing the lookup function. If you have the cell address, it's just a pointer to that lookup cell, and you'd likely be needing that for some reason such as finding the contents ...which you already have. I'm wondering then what other purpose?

David.

pradhan · General User Joined: 01 Sep 2006 Posts: 48

I have an enormous table (A10:Z1000) organised in blocks. e.g. row a10:p21 is a block (or a table inside a table) All blocks do not have the same number of rows. These blocks contain numbers , texts, formulas and formatted cells. This big table is continously adjourned by adding new blocks.

In col A are the values that the function VLOOKUP returns. These values are progressive numbers (1, 2, 3 etc. ) that do not coincide with the row numbers of CALC.

The value that VLOOKUP returns identifies a block. So to see the block I need to push PagDn key several , several times befor i get to the block. I copy the block and paste it where i require it.

I hope this explains.

Better solutions are accepted.

DewiCorn · Newbie Joined: 04 Jul 2006 Posts: 2 Location: Ynys Mon

Does the MATCH function do the job?

Dewi

Villeroy · Posted: Mon Oct 30, 2006 12:53 pm Post subject:

Something like:
=HYPERLINK("#"& ADDRESS(MATCH(searchValue;searchVector;0);1))
where 1 stands for 1st column A
or with sheet name
=HYPERLINK("#Sheet1."& ADDRESS(MATCH(searchValue;searchVector;0);1))
with some display label:
=HYPERLINK("#Sheet1."& ADDRESS(MATCH(searchValue;searchVector;0);1);IF(ISNA(MATCH(searchValue;searchVector;0));"Not Found!";"Push Me"))
adding a cell-style
=HYPERLINK("#Sheet1."&ADDRESS(MATCH(searchValue;searchVector;0);1);IF(ISNA(MATCH(searchValue;searchVector;0));"Not Found!";"Push Me"))&T(STYLE("myHyperlink"))
where myHyperlink is some cell-style blue, italic, underline,...
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

David · Posted: Mon Oct 30, 2006 1:35 pm Post subject:

eduzs · Posted: Mon Oct 30, 2006 3:35 pm Post subject:

Instead of VLOOKUP, if you can, use =CORRESP("FindMe";A3:A10;0) will give you the line number of "FindMe" in the search area, so you can use =INDIRECT("A"+X) to refer to other cells in relative position, for example INDIRECT("A3") = A3, X can be the result of CORRESP function cell, now you can play to see if you find what you want.

I don't knbow the exact function name in english, but these functions is in the group of spreadsheet functions.

pradhan · General User Joined: 01 Sep 2006 Posts: 48

Villeroy · Posted: Tue Oct 31, 2006 1:44 am Post subject:

#NA in english is that kind of error you get with =MATCH("nonsense";A1:A2;0) The formula is syntactically correct and there are no errors in A1:A2, but the desired value "nonsense" is simply not there. This error is somehow "weaker" than the others, so it has it's own function and you can generate it with =NA().
ISNA() -->TRUE if #NA
ISERROR() --> TRUE if any error
ISERR() -->TRUE if any error, except NA()
Try a correct match() on non-existing value and/or use the formula-wizzard (Ctrl+F2) and have a look at function-category "Information".
Second question: If you like to use any other language(s) with openoffice.org, there are language-packs availlable.
http://www.oooforum.org/forum/viewtopic.phtml?t=45386&highlight=language+pack
Linux-distributions have them integrated in their packaging system, on Windows you have to do the usual download-and-exe.
If formula translation is your only problem: Have a look at my <signature> below. Copy the code, Menu:Tools>Macros>Organize
Browse to Container:"My Macros">Library"Standard", button "New", accept default module name and replace the existing code "Sub Main...End Sub" with the code in your clipboard. Call with Tools>Macros>Execute... or customize it. If my OOo runs in german mode, I call this with Ctrl+Shift+F1 (see Tools>Customize).
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

pradhan · General User Joined: 01 Sep 2006 Posts: 48

The hyperlink becomes inactive when the SearchValue changes. My search value is VLOOKUP(). Though it does not happen all the time.

It happens after VLOOKUP() returns #NA value.

Is it nornal ?

Villeroy · Posted: Tue Oct 31, 2006 3:15 am Post subject: