Excel formula

[k.roberts]

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

 

[JE McGimpsey]

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

 

[Guest]

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.

 

[Guest]

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

 

[Peo Sjoblom]

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