Data from One Column Generates Another

[SkollDevours]

Hi all,

I feel rather silly asking this but I have no idea how to phrase my question into google to find the answer.

I'm attempting to create a form that when I fill in the Suburb it populates the post code and MMM-Classification for that Suburb. In a separate spreadsheet (pictured here) I have copied all of this data however I'm unsure of the function I should enter to automatically populate the post code and MMM-Classification when I've entered the Suburb. Typically I've done similar functions using Nested If, however there are 2977 rows worth of data that I'm hoping I can pull and putting something as simple as B1:B2977 just isn't cutting it with Nested if.

For smaller tables I've previously put in something to the effect of: =IF(A1='Spreadsheet 2'A1,B1,IF(A2='Spreadsheet 2'A2,B2...

In the image below I've mocked up a quick show of what I'm hoping to do. I would like a formula that can recognise that if the Suburb is Agnes and it can find Agnes in the Suburb/Locality column that is should then pull the data of the same row but but in the appropriate column.
Please help, I'm going crazy thinking there has to be a simple answer!

 

[sandy666]

look at INDEX / MATCH

INDEX and MATCH in Excel

Use INDEX and MATCH in Excel and impress your boss. Instead of using VLOOKUP, use INDEX and MATCH. To perform advanced lookups, you'll need INDEX and MATCH.

www.excel-easy.com

 

[Peter_SSs]

Welcome to the MrExcel board!

For future questions I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Try this, copied across and down (check the table name matches yours)

SkollDevours.xlsm
	A	B	C	D	E	F	G	H
1	State/Territory	Suburb/Locality	Postcode	MMM classification
2	Victoria	ABBEYARD	3001	5		Airly	3005	4
3	Victoria	ADA	3002	6		Agnes	3003	3
4	Victoria	AGNES	3003	3
5	Victoria	AIRE VALLEY	3004	2
6	Victoria	AIRLY	3005	4
Sheet1

Cell Formulas
Range	Formula
G2:H5	G2	=IF($F2="","",VLOOKUP($F2,Table1[[Suburb/Locality]:[MMM classification]],COLUMNS($F2:G2),0))

 

[SkollDevours]

Hey Peter, with the help you gave I was able to make it work. Thank you so much.
Unfortunately, my version of Excel won't allow for the XL2BB add in. I believe it's because my work's Microsoft suite is temporarily out of license. If I can though I will in the future.

 

[Peter_SSs]

Hey Peter, with the help you gave I was able to make it work. Thank you so much.

You're welcome. Thanks for the follow-up.