Excel puzzler

beefcarrot

I hope this makes sense, so here goes...
I am using Excel to take certain data about a group of people from one sheet to the other. I have used the CONCATENATE function to combine names, then the VLOOKUP command to help find what I am looking for. I have then copied the function down as far as I need to. The problem is that there are some names that copy accross fine, whilst other names give a #NA error. There seems to be no rhyme or reason to this. Sorting clears some of the problems, but not all.

Any help would be grateful received!

johnmc

VLOOKUP will only work if the name is exactly the same on both sheets. Any uppercase, extra spaces etc will cause issues.

Far better to associate a number with each person and VLOOKUP on that.

For specific help on any Office product I would recommend registering with Utter Access, which is FREE.

rumpelstiltskin_2

Are there any number involved?

It could be that in the look up table they are formatted as text whilst in the data table as numbers (or vice versa).

Also you sometmes get a problem when you are looking up a vcalculated field. It may help if you paste the results as values then look up on this.

jessicamb

Have you fixed the parameters withing the vlookup formula correctly?

For example if you did:

=vlookup(x,A1:F50,2,0)

and then copied the formula down the A1:F50 bit would change. If you have $A$1:$F$50 wherever you copy the formula to it will always look at the same data set.

beefcarrot

I've made sure that it's an absolute reference for the table and have changed the names to numbers. Still no joy!

Chippy_Minton

Watch for trailing spaces (and leading, though they are easier to spot). Use the TRIM function to remove them.

Can you post the formula and say a bit more about how the data is arranged?

hami_2

useful link for excel tips:

http://www.contextures.com/tiptech.html

beefcarrot

In a nutshell. I have created a register of Gifted and Talented children for my school. I have started with a Shadow register with lots of parameters and need to reduce it to less headings for a DfES top 10% list. I have concatenated the first and surnames of the children as a reference. The formua I've used is:
=IF(A13="","",VLOOKUP(C13,Shadow!$B$8:$BR4216,4,1))
As I said before, some names work while others don't- for example Billy Bathgate is fine but Aled Jones gives the #NA error. No, these are not real names from my school!!!

beefcarrot

I've just noticed that it isn't an absolute reference in the second number of the table range. I've corrected this on my grid with no effect. Darn!

Chippy_Minton

beefcarrot wrote: »

=IF(A13="","",VLOOKUP(C13,Shadow!$B$8:$BR4216,4,1))

So C13 contains the concatenation of first name and surname, with a space in between?

The 4th parameter of VLOOKUP should be 0 or FALSE, since you want an exact match.

Try clicking on a #N/A cell then the exclamation mark next to it and then Show Calculation Steps - it might give you a clue.

jandanjan

My husband isn't bad at Excel but can never get lookup to work.

So he uses this (looks complicated but isn't, honestly!!)

=index(1,match(2,3,0))

where 1 is the column that contains the data you want to import
2 is the first cell in the column of your matching criteria
3 is the column of data in the same dataset as 1 and which is the cross -reference link
the 0)) ends the formula

eg =INDEX(F:F,MATCH(A1,E:E,0))

Most of this Excel will fill in for you when you click in the spreadsheet/s. I Think you can use the Fxfunction to assist (not tried it).

The data on the left is the result: the data on the right the data to be attached. (edit: the format in the reply didn't look good so have split it vertically)

results page + data cross-matched
1 h
2 s
3 g
4 d
5 f
6 a

source data
6 a
2 s
4 d
5 f
3 g
1 h


The formula will work across diff tabs of the same spreadsheet

=INDEX(Sheet2!B:B,MATCH(Sheet1!A1,Sheet2!A:A,0))

(an angel has got in here somehow - A : A without the spaces!!)

or different spreadsheets.

=INDEX([Book2]Sheet1!$B:$B,MATCH(A1,[Book2]Sheet1!$A:$A,0))

If there is no match then the result will be #N/A.

Note: if there are multiple values for the same data match the result will be for the first time the cross match is found, i.e. if you 'source' data has val 6 = a OR (elsewhere) h the the first one will be reported each time.

As a previous poster has said it must be a perfect match of data - no extra spaces, etc. Try Edit>Replace function to remove if in doubt.

If you're still struggling then mail me and I'll get hubby to look at it for you...

Jan