Reference/Lookup question

Q

quick_question

New Member
Joined
May 31, 2011
Messages
32
Is there a formula that will reference a lookup, compare 2 numbers, and return the referenced column of the highest number.

ie

I want to look up the most recent price of a particular material.

I would like to enter a material # in say E2, and have it return the most recent price for material # 1234.

Is there a formula that would find material 1234, compare all PO#'s (in this case 2&4) and return the price for the highest PO# (here it would be 1)?

--A-------------B-------C
1-Material #----PO#----Price
2-1234----------2-------4
3-5678----------3-------5
4-5378----------5-------3
5-3892----------8-------2
6-1234----------4-------1

Thanks for your help.
 
Sort by date Sort by votes
GK039 said:
Hi Aladin,

Could you please explain the logic behind using Match(1...) in the row argument of the INDEX function?

Thanks

George
Click to expand...
It might be easier to understand if you can see a graphic depiction of what's happening.

Let's assume we have this data:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 37px"><COL style="WIDTH: 37px"><COL style="WIDTH: 37px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">F</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Bill</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">I</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">C</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Sue</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">G</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Tom</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">K</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">D</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Amy</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Tim</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">K</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Joe</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">B</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">G</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Lisa</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">K</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">D</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Bob</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">B</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Sam</TD></TR></TBODY></TABLE>


We want to find the name in column C where both column A = A and column B = A.

This would be the formula we use:

Array entered**:

=INDEX(C2:C10,MATCH(1,IF(A2:A10="A",IF(B2:B10="A",1)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

The answer we want will correspond to the position of our lookup_value of 1.

Here's how we get there.

First we test column A to see if the cells contain "A":

This expression will return either TRUE or FALSE:

IF(A2:A10="A"

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 37px"><COL style="WIDTH: 37px"><COL style="WIDTH: 101px"><COL style="WIDTH: 37px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">IF A2:A10 = A</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">F</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">TRUE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Bill</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">I</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">C</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Sue</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">G</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Tom</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">K</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">D</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Amy</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">TRUE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Tim</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">K</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">TRUE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Joe</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">B</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">G</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Lisa</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">K</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">D</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Bob</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">B</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Sam</TD></TR></TBODY></TABLE>


Then we test column B to see if the cells contain "A".

This expression will return either TRUE or FALSE:

IF(B2:B10="A"

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 37px"><COL style="WIDTH: 37px"><COL style="WIDTH: 101px"><COL style="WIDTH: 99px"><COL style="WIDTH: 37px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">IF A2:A10 = A</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">IF B2:B10 = A</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">F</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">TRUE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Bill</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">I</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">C</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Sue</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">G</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">TRUE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Tom</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">K</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">D</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Amy</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">TRUE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">TRUE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Tim</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">K</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">TRUE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Joe</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">B</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">G</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Lisa</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">K</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">D</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Bob</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">B</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">TRUE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Sam</TD></TR></TBODY></TABLE>


The way we have the nested IFs structured, they evaluate like this...

IF A2 = A and B2 = A, return 1, otherwise return FALSE
IF A3 = A and B3 = A, return 1, otherwise return FALSE
IF A4 = A and B4 = A, return 1, otherwise return FALSE
IF A5 = A and B5 = A, return 1, otherwise return FALSE
IF A6 = A and B6 = A, return 1, otherwise return FALSE
IF A7 = A and B7 = A, return 1, otherwise return FALSE
IF A8 = A and B8 = A, return 1, otherwise return FALSE
IF A9 = A and B9 = A, return 1, otherwise return FALSE
IF A10 = A and B10 = A, return 1, otherwise return FALSE

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 37px"><COL style="WIDTH: 37px"><COL style="WIDTH: 101px"><COL style="WIDTH: 99px"><COL style="WIDTH: 184px"><COL style="WIDTH: 37px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">IF A2:A10 = A</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">IF B2:B10 = A</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">IF A2:A10 and B2:B10 = A</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">F</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">TRUE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Bill</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">I</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">C</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Sue</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">G</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">TRUE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Tom</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">K</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">D</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Amy</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">TRUE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">TRUE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Tim</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">K</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">TRUE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Joe</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">B</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">G</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Lisa</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">K</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">D</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Bob</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">B</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">TRUE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">FALSE</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Sam</TD></TR></TBODY></TABLE>


The results of the two nested IF functions then become the lookup_array in the MATCH function.

So, we want to find the location of the lookup_value 1 within the lookup_array:

=INDEX(C2:C10,MATCH(1,{FALSE;FALSE;FALSE;FALSE;1;FALSE;FALSE;FALSE;FALSE},0))

The lookup_value 1 is found at position 5 of the lookup_array. This value is then passed to the INDEX function:

=INDEX(C2:C10,5)

Return the value at positon 5 from the range C2:C10:

C2 = position 1
C3 = position 2
C4 = position 3
C5 = position 4
C6 = position 5
C7 = position 6
C8 = position 7
C9 = position 8
C10 = position 9

Position 5 = C6, C6 = Tim

So, the array formula:

=INDEX(C2:C10,MATCH(1,IF(A2:A10="A",IF(B2:B10="A",1)),0))

Returns: Tim
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi Biff,

Thanks a lot for your input. Aladin and you helped with this one because if you see my solution to the OP's problem I followed a different logic to get to the correct result which tested the conditions one by one, while yours is somehow faster should I say?

George

T. Valko said:
It might be easier to understand if you can see a graphic depiction of what's happening.

Let's assume we have this data:

Sheet1

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Verdana,Arial; background-color: rgb(255, 255, 255);" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 37px;"><col style="width: 37px;"><col style="width: 37px;"></colgroup><tbody><tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"><td>
</td><td>A</td><td>B</td><td>C</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="border: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-right: 1px solid rgb(0, 0, 0); border-width: 1px; border-top: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">F</td><td style="border-right: 1px solid rgb(0, 0, 0); border-width: 1px; border-top: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Bill</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">I</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">C</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Sue</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">G</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Tom</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">K</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">D</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Amy</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Tim</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">K</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Joe</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">B</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">G</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Lisa</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">K</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">D</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Bob</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">B</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Sam</td></tr></tbody></table>


We want to find the name in column C where both column A = A and column B = A.

This would be the formula we use:

Array entered**:

=INDEX(C2:C10,MATCH(1,IF(A2:A10="A",IF(B2:B10="A",1)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

The answer we want will correspond to the position of our lookup_value of 1.

Here's how we get there.

First we test column A to see if the cells contain "A":

This expression will return either TRUE or FALSE:

IF(A2:A10="A"

Sheet1

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Verdana,Arial; background-color: rgb(255, 255, 255);" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 37px;"><col style="width: 37px;"><col style="width: 101px;"><col style="width: 37px;"></colgroup><tbody><tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="border: 1px solid rgb(0, 0, 0); color: rgb(255, 255, 255); text-align: center;">_</td><td style="border-right: 1px solid rgb(0, 0, 0); border-width: 1px; border-top: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); color: rgb(255, 255, 255); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">_</td><td style="border-right: 1px solid rgb(0, 0, 0); border-width: 1px; border-top: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">IF A2:A10 = A</td><td style="border-right: 1px solid rgb(0, 0, 0); border-width: 1px; border-top: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); color: rgb(255, 255, 255); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">_</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">F</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">TRUE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Bill</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">I</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">C</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Sue</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">G</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Tom</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">K</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">D</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Amy</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">TRUE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Tim</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">K</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">TRUE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Joe</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">B</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">G</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Lisa</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">K</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">D</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Bob</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">B</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Sam</td></tr></tbody></table>


Then we test column B to see if the cells contain "A".

This expression will return either TRUE or FALSE:

IF(B2:B10="A"

Sheet1

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Verdana,Arial; background-color: rgb(255, 255, 255);" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 37px;"><col style="width: 37px;"><col style="width: 101px;"><col style="width: 99px;"><col style="width: 37px;"></colgroup><tbody><tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="border: 1px solid rgb(0, 0, 0); color: rgb(255, 255, 255); text-align: center;">_</td><td style="border-right: 1px solid rgb(0, 0, 0); border-width: 1px; border-top: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); color: rgb(255, 255, 255); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">_</td><td style="border-right: 1px solid rgb(0, 0, 0); border-width: 1px; border-top: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">IF A2:A10 = A</td><td style="border-right: 1px solid rgb(0, 0, 0); border-width: 1px; border-top: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">IF B2:B10 = A</td><td style="border-right: 1px solid rgb(0, 0, 0); border-width: 1px; border-top: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); color: rgb(255, 255, 255); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">_</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">F</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">TRUE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Bill</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">I</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">C</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Sue</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">G</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">TRUE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Tom</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">K</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">D</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Amy</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">TRUE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">TRUE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Tim</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">K</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">TRUE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Joe</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">B</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">G</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Lisa</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">K</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">D</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Bob</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">B</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">TRUE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Sam</td></tr></tbody></table>


The way we have the nested IFs structured, they evaluate like this...

IF A2 = A and B2 = A, return 1, otherwise return FALSE
IF A3 = A and B3 = A, return 1, otherwise return FALSE
IF A4 = A and B4 = A, return 1, otherwise return FALSE
IF A5 = A and B5 = A, return 1, otherwise return FALSE
IF A6 = A and B6 = A, return 1, otherwise return FALSE
IF A7 = A and B7 = A, return 1, otherwise return FALSE
IF A8 = A and B8 = A, return 1, otherwise return FALSE
IF A9 = A and B9 = A, return 1, otherwise return FALSE
IF A10 = A and B10 = A, return 1, otherwise return FALSE

Sheet1

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Verdana,Arial; background-color: rgb(255, 255, 255);" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 37px;"><col style="width: 37px;"><col style="width: 101px;"><col style="width: 99px;"><col style="width: 184px;"><col style="width: 37px;"></colgroup><tbody><tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="border: 1px solid rgb(0, 0, 0); color: rgb(255, 255, 255); text-align: center;">_</td><td style="border-right: 1px solid rgb(0, 0, 0); border-width: 1px; border-top: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); color: rgb(255, 255, 255); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">_</td><td style="border-right: 1px solid rgb(0, 0, 0); border-width: 1px; border-top: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">IF A2:A10 = A</td><td style="border-right: 1px solid rgb(0, 0, 0); border-width: 1px; border-top: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">IF B2:B10 = A</td><td style="border-right: 1px solid rgb(0, 0, 0); border-width: 1px; border-top: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">IF A2:A10 and B2:B10 = A</td><td style="border-right: 1px solid rgb(0, 0, 0); border-width: 1px; border-top: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); color: rgb(255, 255, 255); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">_</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">F</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">TRUE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Bill</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">I</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">C</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Sue</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">G</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">TRUE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Tom</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">K</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">D</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Amy</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">TRUE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">TRUE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">1</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Tim</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">K</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">TRUE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Joe</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">B</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">G</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Lisa</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">K</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">D</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Bob</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">B</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">A</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">TRUE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">FALSE</td><td style="border-width: 1px; border-right: 1px solid rgb(0, 0, 0); border-color: rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Sam</td></tr></tbody></table>


The results of the two nested IF functions then become the lookup_array in the MATCH function.

So, we want to find the location of the lookup_value 1 within the lookup_array:

=INDEX(C2:C10,MATCH(1,{FALSE;FALSE;FALSE;FALSE;1;FALSE;FALSE;FALSE;FALSE},0))

The lookup_value 1 is found at position 5 of the lookup_array. This value is then passed to the INDEX function:

=INDEX(C2:C10,5)

Return the value at positon 5 from the range C2:C10:

C2 = position 1
C3 = position 2
C4 = position 3
C5 = position 4
C6 = position 5
C7 = position 6
C8 = position 7
C9 = position 8
C10 = position 9

Position 5 = C6, C6 = Tim

So, the array formula:

=INDEX(C2:C10,MATCH(1,IF(A2:A10="A",IF(B2:B10="A",1)),0))

Returns: Tim
Click to expand...
 
Upvote 0
GK039 said:
Hi Biff,

Thanks a lot for your input. Aladin and you helped with this one because if you see my solution to the OP's problem I followed a different logic to get to the correct result which tested the conditions one by one, while yours is somehow faster should I say?

George
Click to expand...
All of our suggestions are relatively equal in calculation speed. The differences are negligible.

However, your formula could return an incorrect result if the max PO number for the lookup material is duplicated for another material. For example:

Book1
ABC
1Material #PO#Price
2123424
3567845
4537853
5389282
6123441
Sheet2

With that sample data your formula returns 5 while the correct result is 1.

You can fix that like this:

=INDEX(C2:C6,MATCH(MAX(IF(A2:A6=E1,B2:B6,0)),IF(A2:A6=E1,B2:B6),0))
 
Upvote 0
Thanks a lot for your help and pointing out the possible fault

George

T. Valko said:
All of our suggestions are relatively equal in calculation speed. The differences are negligible.

However, your formula could return an incorrect result if the max PO number for the lookup material is duplicated for another material. For example:

Sheet2

<table style="font-family: Verdana,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 74px;"><col style="width: 72px;"><col style="width: 72px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>A</td><td>B</td><td>C</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="text-align: center; border: 1px solid rgb(0, 0, 0);">Material #</td><td style="text-align: center; border-top: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">PO#</td><td style="text-align: center; border-top: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">Price</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">1234</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">2</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">4</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">5678</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">4</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">5</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">5378</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">5</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">3</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">3892</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">8</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">2</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">1234</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">4</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">1</td></tr></tbody></table>


With that sample data your formula returns 5 while the correct result is 1.

You can fix that like this:

=INDEX(C2:C6,MATCH(MAX(IF(A2:A6=E1,B2:B6,0)),IF(A2:A6=E1,B2:B6),0))
Click to expand...
 
Upvote 0
I used the following equation, and worked great!

=INDEX($C$2:$C$16,MATCH(MAX(IF($A$2:$A$16=$E$1,$B$2:$B$16,0)),$B$2:$B$16,0))

Is there a way where I could compare the same information, but instead of returning the price for the material with the highest PO #, it would return the price for the material with the lowest PO#??

Thanks again everyone for your help.
 
Upvote 0
Hi,

I am glad we helped you. Please take care, since you used my approach, that the solution I provided does not handle dups. Look Aladin's and T.Valko's suggestions as well, as they deal with dups.

For the second part of your question MIN function should do the opposite of MAX

George

quick_question said:
I used the following equation, and worked great!

=INDEX($C$2:$C$16,MATCH(MAX(IF($A$2:$A$16=$E$1,$B$2:$B$16,0)),$B$2:$B$16,0))

Is there a way where I could compare the same information, but instead of returning the price for the material with the highest PO #, it would return the price for the material with the lowest PO#??

Thanks again everyone for your help.
Click to expand...
 
Upvote 0
quick_question said:
I used the following equation, and worked great!

=INDEX($C$2:$C$16,MATCH(MAX(IF($A$2:$A$16=$E$1,$B$2:$B$16,0)),$B$2:$B$16,0))

Is there a way where I could compare the same information, but instead of returning the price for the material with the highest PO #, it would return the price for the material with the lowest PO#??

Thanks again everyone for your help.
Click to expand...

Either the following pair...

Control+shift+enter, not just enter:

=INDEX($C$2:$C$6,MATCH(1,IF($A$2:$A$6=E1,IF($B$2:$B$6=MAX(IF($A$2:$A$6=E1,$B$2:$B$6)),1)),0))

=INDEX($C$2:$C$6,MATCH(1,IF($A$2:$A$6=E1,IF($B$2:$B$6=MIN(IF($A$2:$A$6=E1,$B$2:$B$6)),1)),0))

or, following George's suggestion but modified:

=INDEX($C$2:$C$6,MATCH(MAX(IF($A$2:$A$6=E1,$B$2:$B$6)),IF($A$2:$A$6=E1,$B$2:$B$6),0))

=INDEX($C$2:$C$6,MATCH(MIN(IF($A$2:$A$6=E1,$B$2:$B$6)),IF($A$2:$A$6=E1,$B$2:$B$6),0))
 
Upvote 0
quick_question said:
I used the following equation, and worked great!

=INDEX($C$2:$C$16,MATCH(MAX(IF($A$2:$A$16=$E$1,$B$2:$B$16,0)),$B$2:$B$16,0))

Is there a way where I could compare the same information, but instead of returning the price for the material with the highest PO #, it would return the price for the material with the lowest PO#??

Thanks again everyone for your help.
Click to expand...
Try this...

Book1
ABCDE
1Material #PO#Price_1234
2123424_4
3567835__
4537853__
5389282__
6123441__
Sheet1

Array formula** entered in E2:

=INDEX(C2:C6,MATCH(MIN(IF(A2:A6=E1,B2:B6)),IF(A2:A6=E1,B2:B6),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
 
Upvote 0
I have used the formula provided and it works great, Thanks again all.

As always, there's a need for an iteration to this formula.

Can I compare the PO #s and return the price of the largest one, but this time use 2 criteria.

So before, we had this, where E1 was my criteria, and E2 was what the solution would return:

Sheet1

<table style="font-family: Verdana,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 72px;"><col style="width: 72px;"><col style="width: 72px;"><col style="width: 37px;"><col style="width: 72px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="text-align: center; border: 1px solid rgb(0, 0, 0);">Material #</td><td style="text-align: center; border-top: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">PO#</td><td style="text-align: center; border-top: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">Price</td><td style="color: rgb(255, 255, 255); text-align: center; border-top: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: center; border-top: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">1234</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="background-color: rgb(204, 255, 204); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">1234</td><td style="background-color: rgb(204, 255, 204); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">2</td><td style="background-color: rgb(204, 255, 204); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">4</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">4</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">5678</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">3</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">5</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">5378</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">5</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">3</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">3892</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">8</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">2</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">1234</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">4</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">1</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td></tr></tbody></table>
Current formula for 1 criteria:
=INDEX(C2:C10,MATCH(MAX(IF(A2:A10=E1,B2:B10,0)),IF(A2:A10=E1,B2:B10),0))
Enclosed by Control, Shift, & Enter

This time, imagine the columns shift to the right; column A now has Vendor #'s just as material....column B is Material #, C is PO# and etc.

I'm trying to do the same thing as before, just add an additional criteria.

Thanks again for everyone's help...Much Appreciated!
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Extracting a specific number of characters from a multiline cell
Replies
7
Views
369
etaf
etaf
C
  • Question Question
ComboBox and TextBox
Replies
0
Views
345
Cr4ZyPP
C
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
419
STARL0RD
S
S
  • Solved
Concatenate Numerous rows based off cells value
Replies
4
Views
336
hagia_sofia
hagia_sofia
M
  • Question Question
Trouble with a formula returning duplicate values
2
Replies
19
Views
995
Cubist
Cubist

Forum statistics

Threads
1,224,587
Messages
6,179,736
Members
452,940
Latest member
Lawrenceiow

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top