Formula for locating cell in another sheet?

Hi,
I am having trouble with creating a formula that locates a word that is entered in sheet1, it then finds that word on sheet2, then moves across 3 cells and down 4 cells in sheet2, and then prints the number found in that cell back into another cell in sheet 1. I hope that makes sense. Hopefully someone out there can help.

Yappa.

Yes, it can be done (I think) but need a little more information.
1. that locates a word that is entered in sheet1,
By this do you mean looks in a specific cell for the word? Where are these words? (or is this a single occurance of the word?)

2. then finds that word on sheet2,
Do you have a specific range (e.g. A2:G500)??

The final bit should be managable with the OFFSET function.

ChemistB

In sheet1, the user writes a word in C2, the formula will then be written in D3, which uses the word entered in C2, searches for that word in Sheet2 over range A1:P100, then once it finds that word shifts over 3 cells and down 4 cells and returns the number found in that cell to D3 in sheet1. Hopefully that's a little clearer.

Not to confuse you anymore, but i also forgot to mention once the cell in question has been found, it needs to add itself to the cell beneath it and divide by 2.

Yappa.

Try...

D3:

=SUM(OFFSET(INDEX(Sheet2!A1:P100,MIN(IF(Sheet2!A1:P100=C2,ROW(Sheet2!A1:P100)-ROW(Sheet2!A1)+1))+4,MATCH(C2,INDEX(Sheet2!A1:P100,MIN(IF(Sheet2!A1:P100=C2,ROW(Sheet2!A1:P100)-ROW(Sheet2!A1)+1)),0),0)+3),0,0,2))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

I'm having trouble searching through a 2 dimensional array for your word and the part that you just added on would require VBA so I'm going to hand this one over to someone else.

ChemistB

Thanks for your time Domenic and ChemistB. I'll have a look at this formula tomorrow and get back to you.

Yappa

Originally Posted by Yappa

Thanks for your time Domenic and ChemistB. I'll have a look at this formula tomorrow and get back to you.

Yappa

You're very welcome! By the way, I just realized that the sum needs to be divided by 2, so add...

/2

...at the end of the formula.

Hope this helps!