Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
|
|||
|
|||
use VLOOKUP
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why does Excel show a formula in ONE cell ? | Excel Discussion (Misc queries) | |||
Excel CHALLENGE...3 questions(indirect,rank,array formula)... | Excel Worksheet Functions | |||
converting formula from lotus.123 to excel | Excel Worksheet Functions | |||
How do I get the formula bar in excel 2003 | Excel Discussion (Misc queries) | |||
Suddenly Excel can't calculate formula!!! | Excel Worksheet Functions |