Find Address of a Range based on Text input

[Junarkar]

Hi

Is it possible to find the corresponding range address based on the text input.

I have a list of showrooms and its values of order booking, billing etc. for a month.
In a table if I input the name of a showroom, can I get the corresponding address of the order booking/billing.

I know my message is not clear. I have attached the sample file.

Appreciate the help.

Thanks you.

 

[shrivallabha]

I am not 100% sure about your requirement.

In Sheet2, cell B2 insert following formula and copy down:
=CONCATENATE(ADDRESS(MATCH(Sheet2!A2,SOUTH!A:A,0)+3,COLUMN(H1),,,"SOUTH"),":",ADDRESS(MATCH(Sheet2!A2,SOUTH!A:A,0)+3,COLUMN(AK1)))

In Sheet2, cell C2 insert following formula and copy down:
=CONCATENATE(ADDRESS(MATCH(Sheet2!A2,SOUTH!A:A,0)+4,COLUMN(H1),,,"SOUTH"),":",ADDRESS(MATCH(Sheet2!A2,SOUTH!A:A,0)+4,COLUMN(AK1)))

Does this give you what you want?

 

[Junarkar]

I am not 100% sure about your requirement.

In Sheet2, cell B2 insert following formula and copy down:
=CONCATENATE(ADDRESS(MATCH(Sheet2!A2,SOUTH!A:A,0)+3,COLUMN(H1),,,"SOUTH"),":",ADDRESS(MATCH(Sheet2!A2,SOUTH!A:A,0)+3,COLUMN(AK1)))

In Sheet2, cell C2 insert following formula and copy down:
=CONCATENATE(ADDRESS(MATCH(Sheet2!A2,SOUTH!A:A,0)+4,COLUMN(H1),,,"SOUTH"),":",ADDRESS(MATCH(Sheet2!A2,SOUTH!A:A,0)+4,COLUMN(AK1)))

Does this give you what you want?

Thanks alot Shrivallabha... That perfectly worked.... And I learned some new tricks because of you...

Thanks