Excel XP question, working with Array's

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

J.Heegsma - 31 Jul 2004 13:30 GMT

Hello Guys/Girls,

I have the following problem.

I have one main-list (Row A and B) with all member numbers. Also I got a
list (Row K and L) which contains a second List with financial information
and a part of the numbers of the members-list.

I would like to combine this. Is there een option, like the if-function,
which could check the mainlist with the second-list and if there has been
found a match, the financial information put in Colum C behind the right
member number?.

Greetings from the Netherlands,
Johan Heegsma

Reply to this Message

JulieD - 31 Jul 2004 13:45 GMT

Hi Johan

could you clarify why columns A & B contain member numbers ... is it because
you have more than 65,536 members so you've used up all of column A and
moved onto column B or in which case what happens if there is a match for
both column A & B in columns K & L - what do you want to see in C
or is the information in column B related to the information in column A
i will also ask the same questions as above for columns K & L

maybe if you type out a few lines of your data in your reply email it will
give us a better idea of a solution (please do not attach a workbook)

Regards
JulieD

> Hello Guys/Girls,
>
[quoted text clipped - 11 lines]
> Greetings from the Netherlands,
> Johan Heegsma

Reply to this Message

J.Heegsma - 31 Jul 2004 14:06 GMT

Hello ,

Colum A contains the members nummers 639 in total
Colum B contains other information about each member

Situation is as follows:

A B(member since)
123456 1996

Colum K: contains only members numbers who have payed

Colum L: Contains how much they have payed.

For example:
K L

123456 20

Now I would like the following situation

A (member nr) B( Member since) C (Payed)

123456 1996 20

Colum K isn't used anymore.

Johan.

> Hi Johan
>
[quoted text clipped - 26 lines]
> > Greetings from the Netherlands,
> > Johan Heegsma

Reply to this Message

JulieD - 31 Jul 2004 14:09 GMT

Hi Johan

Then Frank's suggestion of the VLOOKUP is the way to go

In cell C2 type

=VLOOKUP(A2,$K$2:$L$1000,2,false)

This means lookup the value in A2 in column K and return the associated
value from L when there is an exact match.

This will only return the first value found for the member number (hopefully
you only have one value per member)

Additionally, you will have to change the cell reference for column L to be
your real last row number.

also when you have finished you will then, if i'm understanding you
correctly, need to change the answers in column C from being a formula to
being a value to do this, select column c - copy it, then choose edit /
paste special - values

Regards

julieD

> Hello ,
>
[quoted text clipped - 58 lines]
> > > Greetings from the Netherlands,
> > > Johan Heegsma

Reply to this Message