Please explain this formula

  • Thread starter Thread starter Iriemon
  • Start date Start date
I

Iriemon

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
 
Iriemon said:
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
Click to expand...


An explanation of the INDEX and MATCH function can be found here:

http://www.contextures.com/xlFunctions03.html
 
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&Sheet2!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:A100=D1)*(Sheet2!B1:B100=D2),),0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
 
Back
Top