Match & offset in one formula error

[excel2007uk]

Hi can anyone help out?

I want to be able to select or type a company name out of around 100, then this will automatically put the companys address in the cells below. Basically trying to find a quicker way of entering the address on letters.

Heres what I have:

=if(match(a16,A49:A149,0),OFFSET(match(a16,A49:A149,0),1,0,1,1,),"Matching Error"))))

I am trying to enter my company name in A8 & it will match it in my table from A49:G149

My Headings are columns:
A(Underneath is customer)

B(Underneath is Company Name)

C(Underneath is Address Line 1)

D(Underneath is Address Line 2)

D(Underneath is Address Line 2)

So what I wanted to do is enter the above formula in A9. Then for it to match the Customer in A with return the value to the right the so A9 = Matched row then two cells to the right A10 = Matched row then 3 cells to the right up to A12.


Bit complicated but unfortunately I cannot upload a photo

EDIT

Customer:

Company Name:

Address Line 1:

Address Line 2:

Address Line 3:

Address Line 4:

POST CODE:

<tbody>
</tbody>

 

[Special-K99]

FYI

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.



Dunno if I've understand this properly but try...

in A9
=IFERROR(VLOOKUP(A16,A49:G149,1,0),"Not found")
in B9
=IFERROR(VLOOKUP(A16,A49:G149,2,0),"")
in C9
=IFERROR(VLOOKUP(A16,A49:G149,3,0),"")
in D9
=IFERROR(VLOOKUP(A16,A49:G149,4,0),"")