Look up a value on another worksheet and return cell row and column number

[Harters]

OK, so, what I need to do is a sort of look up where on sheet 1 in cell B1, i enter a formula that looks for the value in sheet1 cell A1 across all cells in sheet 2. When it finds the required value (which will be a word) somewhere in sheet 2 (it will be a unique word appearing only once in sheet 2), then return the cell row and column number of the cell it found the value in.

I know how to vlookup in the normal way and also how to return a cell row/column by using =CONCATENATE("(",COLUMN(), ", ",ROW(),")") but i can't manage to marry the two together

Sheet 1:
look value on Sheet1 A1 across all of Sheet2 and write cell row and column number in cell B1
look value on Sheet1 A2 across all of Sheet2 and write cell row and column number in cell B2
.........
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apple
[/TD]
[TD](1, 2)
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Dog
[/TD]
[TD](2, 3)
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A (col 1)
[/TD]
[TD]B (Col2)
[/TD]
[TD]C (col3)
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]<blank></blank>
[/TD]
[TD]Apple
[/TD]
[TD]<blank></blank>
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]<blank></blank>
[/TD]
[TD]<blank></blank>
[/TD]
[TD]Dog
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]<blank></blank>
[/TD]
[TD]<blank></blank>
[/TD]
[TD]<blank></blank>
[/TD]
[/TR]
</tbody>[/TABLE]

Any Idea how this can be acheived ??????

Many thanks in advance

 

[Spyros13]

You know what I do what I want to do something like that? (marry the use of vlookup and another function). I create an identifier column.


if(Isnumber(find("TEXT YOUR LOOKING FOR",B1,1),1,"") in the new column, so youve got lots of "" cells and some cells with 1 in them.

1 is what you are vlooking for in and returning the approriate information.


you might need quite a few new columns created. and a few nifty vlookups , but it Does work.

 

[Harters]

Not really following what you mean. The formular needs to only be entered in Sheet1 Cell B1.

It looks across every cell in Sheet2 for whatever value is in Sheet1 Cell A1 and then returns the row/column of the cell in sheet 2 where it found the value held in Sheet 1 cell A1.

I can't have loads of ther rows holding the results...only the result to be in column B on sheet 1.

Maybe you mean having 500 columns all looking for the value in a single respresentative cell in sheet 2 and either displaying "" of 1 and then afterwards another fumular looking acorss the 500 new columsn to see which has a 1 in it and then somehow return the row/column where the 1 is found. This is in theory workable however i still don't know how the additional foumalr that then looks for the cell with the 1 in it will return the row/columns and also this seems just the same as i am trying to do in the first place.

I thank you very much for trying to help but I may be missing the point as i can't see how this will work.

Maybe you can suggest what the forumlas will look like in reality ?

this is the sort of thin I was trying in cell B1 of sheet1:

=LOOKUP(A1,Sheet2!1:1048576,CONCATENATE("(",COLUMN(), ", ",ROW(),")"))

this brings back #VALUE

what I was hoping for was it to find the word Apple somewhere on sheet 2 and return something like (3,6)

 

[billszysz]

="("&TRUNC(SUMPRODUCT(--IF(Sheet2!$A$1:$C$3=A1,ROW(Sheet2!$A$1:$C$3))))&","&SUMPRODUCT(IF(Sheet2!$A$1:$C$3=A1,COLUMN(Sheet2!$A$1:$C$3)))&")"

 

[Harters]

Wow... nice forumla however i pasted it in with double click to cell B1 on sheet1 and ensured the word in A1 on sheet1 existed in C1 of Sheet2 but i get a #value

 

[Harters]

update: the formula works to a point but only if the word we are looking for exists on sheet 2 in cell B1 (remember it could exist anywhere) and then it returns (1,1).

It should return (in this case) (1,2) as cell B1is the first row and 2nd column

 

[billszysz]

oops...sorry....This is array formula so you have to use CTRL+SHIFT+ENTER

 

[Harters]

Brillant - you are a super star - i can't thank you enough ... Please accept 1 million MrExcel Thank you Points to spend in the bar of your choice

 

[billszysz]

You are welcome
Thanks for your feedback.

There was a little mistake in my previous formula (trunc is not need - this was in my older formula and i forgot delete it)
="("&SUMPRODUCT(--IF(Sheet2!$A$1:$C$3=A1,ROW(Sheet2!$A$1:$C$3)))&","&SUMPRODUCT(IF(Sheet2!$A$1:$C$3=A1,COLUMN(Sheet2!$A$1:$C$3)))&")"

 

[Aladin Akyurek]

How do we know that Apple only occurs once in the data?