Lookups with different original sources of data

  • Thread starter Thread starter SEAL2ER
  • Start date Start date
S

SEAL2ER

For my work, I produce monthly funding reports. one set of data comes from an
irsdat(? have i got the letters the wrong way round? i'm sure you know what i
mean!) file, that gets imported into excel (he conversion goes ok, all data is
converted accurately). the second lot of data i use comes from specialised
software that goes through access, which is then imported into the same
spreadsheet but on a diferent worksheet.

both sets of data have reference numbers for students, but some students are in
both data sheets more than once - although the number they are on each sheet
can be different.

I have tried to use a normal lookup function to display data from the .irsdat
file in the other sheet, but it returns the value #na. i cannot seem to make
it say anything else!!! is this because the data sources are influencing the
result? when i maually type in the reference number, it does produce accurate
results, but as there are over 3000 occasions that this will need doing, i just
want to put the cell reference in.

essentially i am trying to use a lookup table to reference data in one
worksheet, and display it in another, in relation to data already present.

can anyone help? please???
 
Hi
it would be helpful if you could provide the following:
- your used formula to lookup values
- some example rows of data for both sheets 8plain text, no attachment)
- Where did you type he reference nmber manually 8into the lookup
formual). If yes, probably the data in your target sheet does not
exactly match the lookup data (e.g. additional spaces, number instead
of text format, etc.)

So please provide a little bit more information

Frank
 
I think I know what is happening, because I struggled
witht this same problem a while back when importing info
from our system into excel. Do you go through the process
of inserting cell breaks during the import? If so you
will want to make the cell break at the right most of the
info area (this is hard to explain). So if you have a
number: eg.DAS158 then some space between it and the next
set of text, you need to put the line break at the
beginning of the next set of text (just before it).
I'm suspecting that you are putting the line break right
after the set of data. What this does is it puts a space
after the data that you are using for the lookup
function. This is why it works when you type it in.
Sorry if this makes no sense, I'm am not a MVP or
anything, I just use excel in my world a lot.
 
Hi Frank
The formula i used originally was
=lookup(Page!learner_ref,other_page!learner_ref:learner_ref,other_page!centre:centre).

However, some bright spark realised that not all learner refs were on
the
other page, so would show the closest value ... which lead to this
formula:
=if(lookup(Page!learner_ref,other_page!learner_ref:learner_ref),lookup(Page!learner_ref,other_page!learner_ref:learner_ref,other_page!centre:centre),"Void").
This produced the value n#a.

Then a Vlookup was used ...
=vlookup(Page!learner_ref,other_page!learner_ref:learner_ref,other_page!centre:centre,5),
or along those lines. That was from my old IT teacher. Didn't work.
I've since given up and am cross-referencing over 3000 learners by
hand because there is no way that i make Excel do it for me!

Examples of the rows in the delimited data:
Learner Ref S/n F/n Course Code Centre Start End
Status
695682 Bloggs Joe 00264986 LC68 01/08/03 14/12/03 3
196345 Smith Will 00255140 LC13 10/10/03 1

Examples of the rows in the Access-imported data:
L/R S/n F/n Course Start End Stat Cor Add Sup
695682 Bloggs Joe 00264986 01/08/03 14/12/03 3 99.87 12.65
196345 Smith Will 00255140 10/10/03 1 10.12 0.00

When i did get an accurate result from the second formula shown, i
actually hand-typed the learner ref into the formula, at the first
learner_ref point. As far as i am aware, all the columns were
correctly formatted ...
Thanks ever so very much for replying!
 
Back
Top