Excel formula
 

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

Excel formula
 

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
In article . com,
wrote:

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

Excel formula
 

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)),"",vlook up(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.

" wrote:

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

Excel formula
 

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

" wrote:

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

Excel formula
 

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


--

Regards,

Peo Sjoblom

"Ashish Mathur" wrote in message
...
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

" wrote:

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

use VLOOKUP