Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please explain this formula
I found this formlua using the search function but cannot understand what it
is saying. =INDEX(C1:C100, MATCH(D1&D2,'Sheet2'!A1:A100&B1:B100,0)) I'm assuming the D1&D2 references the 2 cells I want to match and the 'Sheet2'!A1:A100&B1:B100 are the cells containing my values to match but I'm not clear what the C1:C100 and the ",0" are referring to. How does my table need to be set up? I have the 'Sheet2'!A1:A100&B1:B100 cells filled with my data and 'Sheet2'!F1:F100 containing the value I want it to return. Do I need to set up my table differently? Any help would be greatly appreciated! Thanks Jim |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please explain this formula
Iriemon wrote:
I found this formlua using the search function but cannot understand what it is saying. =INDEX(C1:C100, MATCH(D1&D2,'Sheet2'!A1:A100&B1:B100,0)) I'm assuming the D1&D2 references the 2 cells I want to match and the 'Sheet2'!A1:A100&B1:B100 are the cells containing my values to match but I'm not clear what the C1:C100 and the ",0" are referring to. How does my table need to be set up? I have the 'Sheet2'!A1:A100&B1:B100 cells filled with my data and 'Sheet2'!F1:F100 containing the value I want it to return. Do I need to set up my table differently? Any help would be greatly appreciated! Thanks Jim An explanation of the INDEX and MATCH function can be found he http://www.contextures.com/xlFunctions03.html |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please explain this formula
Some specific thoughts to get you going ..
This expression, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula (CSE): =INDEX(Sheet2!F1:F100,MATCH(D1&D2,Sheet2!A1:A100&S heet2!B1:B100,0)) will return what you seek. This index part: INDEX(Sheet2!F1:F100 is the range that you want the results to be extracted from, for the match found in the MATCH part of it. The zero (or FALSE) param in MATCH( ...,0) specifies it to find an exact match You could also try the alternative, better? expression below which achieves the same results. It's slightly longer, but easier to confirm, and to intuitively understand what's happening, in my opinion. Normal ENTER will do (no need to CSE): =INDEX(Sheet2!F1:F100,MATCH(1,INDEX((Sheet2!A1:A10 0=D1)*(Sheet2!B1:B100=D2),),0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Iriemon" wrote: I found this formlua using the search function but cannot understand what it is saying. =INDEX(C1:C100, MATCH(D1&D2,'Sheet2'!A1:A100&B1:B100,0)) I'm assuming the D1&D2 references the 2 cells I want to match and the 'Sheet2'!A1:A100&B1:B100 are the cells containing my values to match but I'm not clear what the C1:C100 and the ",0" are referring to. How does my table need to be set up? I have the 'Sheet2'!A1:A100&B1:B100 cells filled with my data and 'Sheet2'!F1:F100 containing the value I want it to return. Do I need to set up my table differently? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please explain this formula | Excel Discussion (Misc queries) | |||
Please explain formula to me | Excel Worksheet Functions | |||
Please explain function/formula | Excel Worksheet Functions | |||
Could any one Explain this Formula | Excel Worksheet Functions | |||
Please Explain Formula | Excel Worksheet Functions |