Two Excel sheets trying to match text data and return the match from the second column!

[bankcott]

Hi – need all the help I can get please in simple language as I am new to vlookups and other formula and I need an answer to this fairly quickly (thank you!).

I have two sheets:

Sheet 1
School Name School Area
School A Area A
School B Area B
School C Area C

Sheet 2
School Name School Area Pupil Pupil Address
School B Jack 34 Madeup Street
School B Jo 44 Madeup Street
School A Sam 4 Lala land
School C Roxy 6 Imagine Road

Need to match the school names and put the correct area into sheet 2 if possible.

Tried the vlook up =VLOOKUP(A1,SP!A1:A8,2,FALSE) but this keeps on giving me the REF error. Also tried a =MAX formula which gave me an error too.

Any ideas much appreciated, thank you?

[jeffreybrown]

With you sample data, this works fine for me...

B2 (copied down) =VLOOKUP(A2,Sheet1!$A$2:$B$4,2,FALSE)

[bankcott]

Thanks Jeff - I am now getting a N/A result despite there being a definite match in my first list. I tried =value1=value2 and it comes up TRUE. Not sure if there is a way to attach a file here or I could send you the original to see. I know it is something simple I am missing but it is driving me mad! Thank you for your help.

[jeffreybrown]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[bankcott]

Thanks Jeff. Sample attached for you, I hope!

[jeffreybrown]

You need to include the entire lookup table...

Change this...
=VLOOKUP(A1,'Sheet 1'!A1:A187,2,FALSE)

To this...
=VLOOKUP(A1,'Sheet 1'!A1:B187,2,FALSE)

But since you are going to drag the formula down, include absolute references...

=VLOOKUP(A1,'Sheet 1'!$A$1:$B$187,2,FALSE)

[bankcott]

Thank you so much Jeff ..... so simple but so annoying when you don't spot it. Appreciate your very quick response.

[jeffreybrown]

You're welcome…glad it worked out for you and thanks for the feedback...