if x is a match in this selection then display the info in the column next to it

[Sean_QS]

Hi guys,

I'm fairly simple when it comes to formulas which is why I am posting in this forum. I'm creating a purchase order system for my employer and could use some help. I have a list of suppliers in column C which is on the worksheet named 'PO Settings' and next to that in column D I have addresses and contact info for each of them. On another sheet I am creating a purchase order template and I have made a drop down list for each of the suppliers from that PO Settings worksheet, under that I am looking to put the address which can be found next to column C on the PO Settings worksheet but for the address to automatically match the suppliers name.

Any suggestions?

[FoxSeaLady]

Hi

It should be fairly easybut would be easier if you could upload a desensitised version of your soreadsheet.

Cheers

[Syrkrasi]

you don't necessary need VBA for this. you could achieve this by using either function: Index & Match or VLookup

[Sean_QS]

No problem here you go


https://www.dropbox.com/s/ia1u9tct4s...23V2.xlsx?dl=0

[Syrkrasi]

I removed the Data Validation from PO Tracker column D (Customer Address) and insert the below formula

=INDEX(CONCATENATE('PO Settings'!$E$3:$E$500,", ",'PO Settings'!$F$3:$F$500,", ",'PO Settings'!$G$3:$G$500),MATCH($C14,'PO Settings'!$C$3:$C$500,0))

For C7:C10

=INDEX('PO Settings'!$D$3:$D$500,MATCH($C$6,'PO Settings'!$C$3:$C$500,0))
=INDEX('PO Settings'!$E$3:$E$500,MATCH($C$6,'PO Settings'!$C$3:$C$500,0))
=INDEX('PO Settings'!$F$3:$F$500,MATCH($C$6,'PO Settings'!$C$3:$C$500,0))
=INDEX('PO Settings'!$G$3:$G$500,MATCH($C$6,'PO Settings'!$C$3:$C$500,0))

This is an array formula so do not just press the ENTER key. Press CTRL + SHIFT + ENTER