Excel formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

k.roberts@ucas.ac.uk - 23 Nov 2005 11:52 GMT

I need to create a formula that will compare the contents of cells
A2:A2000, to the contents of cells A2:A2000 in another worksheet
('schools'), and when finding 2 values that match, I need to bring the
contents of the corresponding cell in column B from 'schools' into the
first worksheet.

How do I go about setting this up?

KLR

Reply to this Message

JE McGimpsey - 23 Nov 2005 13:17 GMT

One way:

B2: =IF(A2=Schools!A2,Schools!B2,"")

copy down to B2000.

OTOH, if you want to sum all the Schools!B2:B2000 where
A2:A2000=Schools!A2:A2000 in one cell:

=SUMPRODUCT(--(A2:A2000='Schools'!A2:A2000), Schools!B2:B2000)

or, if you want to exclude blanks:

=SUMPRODUCT(--(A2:A2000=Schools!A2:A2000), --(A2:A2000<>""),
Schools!B2:B2000)

For an explanation of --, see

http://www.mcgimpsey.com/excel/doubleneg.html

> I need to create a formula that will compare the contents of cells
> A2:A2000, to the contents of cells A2:A2000 in another worksheet
[quoted text clipped - 5 lines]
>
> KLR

Reply to this Message

bpeltzer - 23 Nov 2005 13:56 GMT

I think you just need a vlookup; in cell b2 on the sheet you need to
supplement:
=if(isna(vlookup(a2,schools!A:B,2,false)),"",vlookup(a2,schools!A:B,2,false))
This will return the contents of column B from the row in which in finds the
matching entry. If there is no match, the result is blank.

> I need to create a formula that will compare the contents of cells
> A2:A2000, to the contents of cells A2:A2000 in another worksheet
[quoted text clipped - 5 lines]
>
> KLR

Reply to this Message

Ashish Mathur - 23 Nov 2005 14:40 GMT

Hi Roberts,

How are you. I would suggest that you use the index command instead of the
vlookup command because in the vlookup command you run the risk of getting
values for approximate matches. In the index function, you can find exact
matches by specifying 0 in the last syntax of the function. more help is
available inthe Help menu

Regards

> I need to create a formula that will compare the contents of cells
> A2:A2000, to the contents of cells A2:A2000 in another worksheet
[quoted text clipped - 5 lines]
>
> KLR

Reply to this Message

Peo Sjoblom - 23 Nov 2005 15:03 GMT

Not true, vlookup with either FALSE or 0 will look for exact matches,
index by itself doesn't lookup anything, I assume you meant a combination of
index and match where match will have the option for exact match
Only reason to use that in case of an exact match is if the lookup value is
not in the leftmost column

Signature

Regards,

Peo Sjoblom

> Hi Roberts,
>
[quoted text clipped - 15 lines]
> >
> > KLR

Reply to this Message

cs02000 - 23 Nov 2005 17:29 GMT

use VLOOKUP

Signature

cs02000

Reply to this Message