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

 

[Harters]

Apple only appear once - its just a manual thing

Thanks for the updated forumla - i was just going to post a question as I was getting results like (18,1000000000001) instead of (18,10) after I changed the range from Sheet2!$A$1:$C$3 everywhere to be Sheet2!$A$1:$SZ$1000 so hopefully it will now work.

If I can I would also liek the results the other way around? instead of (18,10) it should be (10,18) and another change is to have it in format (10:18)

I'll go try the new formula

 

[Harters]

Oh dear, now it doesn't work...

$C$3 changed to $SZ$1025 to look across bigger range

="("&SUMPRODUCT(--IF(Sheet2!$A$1:$SZ$1025=A1,ROW(Sheet2!$A$1:$SZ$1025)))&","&SUMPRODUCT(IF(Sheet2!$A$1:$SZ$1025=A1,COLUMN(Sheet2!$A$1:$SZ$1025)))&")"

 

[platonic567]

Oh dear, now it doesn't work...

$C$3 changed to $SZ$1025 to look across bigger range

="("&SUMPRODUCT(--IF(Sheet2!$A$1:$SZ$1025=A1,ROW(Sheet2!$A$1:$SZ$1025)))&","&SUMPRODUCT(IF(Sheet2!$A$1:$SZ$1025=A1,COLUMN(Sheet2!$A$1:$SZ$1025)))&")"

Try below formula, it's in the new format i.e (Column:Row) and doesn't need to be array entered:

="("&SUMPRODUCT(--(A1=Sheet2!$A$1:$SZ$1025)*COLUMN(Sheet2!$A$1:$SZ$1025))&":"&SUMPRODUCT(--(A1=Sheet2!$A$1:$SZ$1025)*ROW(Sheet2!$A$1:$SZ$1025))&")"

FYI, if your formula has to be array entered then a simple SUM would suffice in place of SUMPRODUCT.

 

[Harters]

really sorry but neither work. They dont error (array with SUM or normal with SUMPRODUCT) - they both return (0 : 0)
I will keep trying to fiddle with them and let you know

 

[platonic567]

That's weird, it works fine for me.

Btw (0:0) is returned when no match is found. Is it returning (0:0) for all cases?

Try running the formula in a new workbook with just few sample text spread around Sheet2!$A$1:$SZ$1025. See what results the formula gives back.

 

[Harters]

OK, new worksheet and it works perfectly... I'm writting a letter to Bill.... Good old Micorsoft

Many many many thanks to all who have helped this (and a big thanks from the countless homeless you have unknowningly helped)

 

[platonic567]

Hehe....the good ol' method of using a new sheet when something's not working right.

You're welcome and glad we could help.

 

[Aladin Akyurek]

OK, new worksheet and it works perfectly... I'm writting a letter to Bill.... Good old Micorsoft

Many many many thanks to all who have helped this (and a big thanks from the countless homeless you have unknowningly helped)

I don't think SumProduct is a good idea for the risk of having Apple occur more than once is not guaranteed.

 

[Louis Morfomichaniki]

Dears
Is not needed any long formula to do it. You need only HLOOKUP and VLOOKUP.

We have two cases:

At the first we have the VALUE in the working sheet and this VALUE in the background sheet is in a specific known row. We create one more line in the background sheet where we give in each field the number of the column. We use HLOOKUP using the VALUE to get the number of the column in the working sheet and then we use this column in VLOOKUP.

At the second the value is in a specific known column so we start using the VLOOKUP and following the HLOOKUP.