Problem with VLOOKUP query

[dpbarry]

Hi Folks..

I have a spreadsheet with 121 Pupil Names (in format Surname, Forename) (Column A - sorted alphabetically) and Previous School (Column B).

I have another couple of columns that assist in generating a randomised list of the 121 names and put it in Column E.

All works so far up to this point in that the names are all jumbled up

The teacher then asked me if I could also put the previous school into another Column (F) beside the randomised names so I thought I'd use VLOOKUP and I thought I had it working as it was putting the previous school names in BUT, when I studied closed, some of the school names were correct but some were not and I can't figure out why - probably something simple!!.

Basically I asked it to vlookup the name in the randomised cell with the list in Columns A and return the relevant value in Column B. As I say, some are correct but some aren't.

Could anyone assist in guiding me as to what I'm doing wrong.

Many thanks

Declan

 

[RoryA]

What's the formula you are using? Are you specifying the 4th VLOOKUP argument as FALSE?

 

[dpbarry]

What's the formula you are using? Are you specifying the 4th VLOOKUP argument as FALSE?

At moment, I've tackled it from another angle using INDEX - MATCH to see how that works. Teacher just checking the values.

It didn't matter whether I included false or left it out, same results.

Is it still possible to post a snippit of my spreadsheet?

Declan

 

[RoryA]

You can post tables using the usual tools, but it could help if you started by posting the formula you are using. VLOOKUP and INDEX/MATCH will not return different results for the same inputs, assuming they are set up the same.

 

[dpbarry]

Cheers Rory.

I'll modify the data I will post. Just for Data Protection Act purposes. INDEX:MATCH didn't work.

Declan

 

[dpbarry]

Hi Rory.

The equation I'm using is: =VLOOKUP(G2,$A$2:$D$121,4)

G2 = Value

$A$2:$D$121 = List

4 = column info I wish to return

Declan


QUOTE=RoryA;5087463]You can post tables using the usual tools, but it could help if you started by posting the formula you are using. VLOOKUP and INDEX/MATCH will not return different results for the same inputs, assuming they are set up the same.[/QUOTE]

 

[RoryA]

Use:

=VLOOKUP(G2,$A$2:$D$121,4,FALSE)

If you get #N/A errors back, then you know that your lookup values don't match the original table for some reason.

 

[dpbarry]

All coming back with data but for some reason when I manually compare what it has brought back with what the actual value should be, it's different. Not for all, just the odd couple.

Strange!!

Use:

=VLOOKUP(G2,$A$2:$D$121,4,FALSE)

If you get #N/A errors back, then you know that your lookup values don't match the original table for some reason.

 

[RoryA]

Can you give me an actual example? The only possible ways I could see that happening would be if you have duplicate values in the lookup table, or if your lookup values contain wildcard characters like * or ?

 

[dpbarry]

I'm sorting down through the speadsheet to post an example with an area where the data is wrong. As soon as I get it done, I'll post it.

Many thanks for your patience!!

Declan[

QUOTE=RoryA;5087626]Can you give me an actual example? The only possible ways I could see that happening would be if you have duplicate values in the lookup table, or if your lookup values contain wildcard characters like * or ?[/QUOTE]