Find a text string in a sheet and return its location (column, row)

[BobMillar68]

I am trying to convert an invoice summary spreadsheet into something I can import into another system. The original file has data which is located in different places on different tabs so I want to search each tab for a specific text string and then either return the value in the nth cell to the right of the text string, or perhaps use Indirect with a cell reference using the returned column and row). I have not found a way to search and return the field's location.

I have seen a reference to the Range.Find method elsewhere but I am not particularly proficient with VBA so was hoping to just use formulae to achieve the same thing.
I can't use VLOOKUP as I can't predict what column the text will be in.

For example:

In one sheet the text "VAT" may be in cell AH29 and the VAT value in AI29 while in another "VAT" might be in AG30 and the VAT value in AH30.

Thanks

Bob

 

[AlanY]

try this, amend the range to suit

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3
4
5
6				VAT
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21	VAT	$D$6
216

Cell Formulas
Range	Formula
B21	{=ADDRESS(MAX((A1:R20=A21)*ROW(A1:A20)), MAX((A1:R20=A21)*COLUMN(A1:R1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[BobMillar68]

Many Thanks Alan

That looks like it will solve it!

Much appreciated!

Bob

 

[AlanY]

you're welcome