|
|
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
 |
|
|
Thread Tools | Display Modes |
May 17th, 2002, 08:17 PM
|
#1 |
|
New Member
Join Date: May 2002
Posts: 5
|
Is there a way to get the vlookup function to return the cell address, rather than the value, of the lookup?
please also reply to azad.padamsey@boc.jgc.co.jp |
|
|
|
May 17th, 2002, 08:52 PM
|
#2 |
|
Board Regular
Join Date: Feb 2002
Location: Southfield,MI USA
Posts: 1,030
|
G'day,
Have you considered involving the MATCH function? It should get you partway there. Adam |
|
|
|
|
May 17th, 2002, 09:35 PM
|
#3 |
|
MrExcel MVP
Join Date: Feb 2002
Location: San Francisco, California USA
Posts: 10,388
|
Maybe a formula like this will work. Modify for ranges and column reference (I used column A as a hard code in the formula, after the word "cell".
=VLOOKUP(A3,$A$1:$A$3000,1,0)&" Found in cell A"&MATCH(A3,Sheet2!$A$1:Sheet2!$A$3000,0) Any help? |
|
|
|
|
May 18th, 2002, 01:07 AM
|
#4 |
|
New Member
Join Date: May 2002
Posts: 5
|
Tom,
This is what I am trying to achieve: I am using the HLOOKUP function (Excel 2000) .Say for example the value returned is 20.I need to know where the "address" of the value 20 is in (i.e. $c$8).I tried to use the CELL function in the following manner,but no luck. =CELL("address",((HLOOKUP($E$5,$J$11:$HO$185,2,FALSE)))) |
|
|
|
|
May 18th, 2002, 05:03 AM
|
#5 |
|
MrExcel MVP
Join Date: Apr 2002
Location: Vancouver BC , Canada
Posts: 6,259
|
OK Adza:
I've worked out an answer if you don't mind one hidden sheet !  In this solution I have three sheets: (1)"Data" which is your array of Data Range(A:Z) (2)"hidden" Which has one formula dragged from Range A2-Z2 (3) Query sheet A1= The text you want to find, A2= location where text found DATA SHEET : On this sheet just paste your data HIDDEN SHEET: Row 1: drag the column letters across TOP row i.e. A1.value = A, B1.value = B etc. Row 2: Paste the following formula into A2 then drag across to Z2 Code:
=IF(NOT(ISERROR(MATCH('Query sheet'!$A,(Data!A:A),0))),A1&MATCH('Query sheet'!$A,(Data!A:A),0),"")
Cell A1: just type in what text you want to search for Cell A2: Code:
=CONCATENATE(Hidden!2:2)  [ This Message was edited by: Nimrod on 2002-05-18 04:15 ] |
|
|
|
|
May 18th, 2002, 11:43 AM
|
#6 |
|
MrExcel MVP
Join Date: Feb 2002
Location: The Hague
Posts: 50,317
|
This is what I am trying to achieve: I am using the HLOOKUP function (Excel 2000) .Say for example the value returned is 20.I need to know where the "address" of the value 20 is in (i.e. $c$8).I tried to use the CELL function in the following manner,but no luck. =CELL("address",((HLOOKUP($E$5,$J$11:$HO$185,2,FALSE)))) =IF(COUNTIF($J$11:$HO$185,E5),ADDRESS(SUMPRODUCT(($J$11:$HO$185=HLOOKUP(E5,$J$11:$HO$185,2,0))*(ROW($J$11:$HO$185))),SUM PRODUCT(($J$11:$HO$185=HLOOKUP(E5,$J$11:$HO$185,2,0))*(COLUMN($J$11:$HO$185)))),"Not Found") If the HLOOKUP part is properly set up, this (copyrighted) formula will give you the desired address. Aladin [ This Message was edited by: Aladin Akyurek on 2002-05-18 10:46 ] |
|
|
|
|
May 19th, 2002, 05:32 PM
|
#7 |
|
New Member
Join Date: May 2002
Posts: 5
|
Aladin,
Your solution works beautifully.Now I have to study your (copyrighted) formula to understand it properly.So much formula to achieve what is reasonably a simple task. Many Thanks to all who responded. Regards Azad |
|
|
|
|
May 19th, 2002, 05:54 PM
|
#8 | |
|
Join Date: May 2002
Posts: 26
|
Quote:
What do you mean by "(copyrighted) formula" ? |
|
|
|
|
|
May 20th, 2002, 01:56 AM
|
#9 | ||
|
MrExcel MVP
Join Date: Feb 2002
Location: The Hague
Posts: 50,317
|
Quote:
 Aladin |
||
|
|
|
|
May 14th, 2005, 10:06 PM
|
#10 |
|
New Member
Join Date: May 2005
Posts: 12
|
big help
now I the search from A column and return the number "8"
then I want to show the data in B"8" (B*) I've try to use value, but its now working anybody can help |
|
|
|
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
