If column A contains "word" then grab first instance of it and place it in column C

[jeremypyle]

If column A contains a word such as "banana" then grab first instance of it and place it in column C

cell a1-house
cell a2-banana
cell a3-banana
cell a4-rat
cell a5-rat
cell a6-rat


cell b1-17
cell b2-3
cell b3-5
cell b4-9
cell b5-1
cell b6-2

I want cell c1 to say this:
If range a1:a6 contains "banana", then select cell to right of first instance of banana and place that in c1.

To the right of the first instance of banana is b2 which is 3. So in cell c1 would be entered the number "3"

In c2, I want it to grab the number to the right of the second instance of the word banana which is 5.

This is a reasonably complex formula. If you could help, I would much appreciate.

 

[Jerry Sullivan]

This works, although I suspect there is a simpler way to do this.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=0 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 106px"><COL style="WIDTH: 64px"><COL style="WIDTH: 112px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">house</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">17</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">banana</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">3</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">banana</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">5</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">rat</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">9</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">rat</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">rat</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid"></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C1</TD><TD>{=IFERROR(INDEX($B$1:$B$6,SMALL(IF("banana"=$A$1:$A$6,ROW($A$1:$A$6),9.99E+307 ),ROWS($A$1:$A1))),"")}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE></B>
Excel tables to the web - Excel Jeanie Html 4

 

[jeremypyle]

The following formula does not work:
{=IFERROR(INDEX($B$1:$B$6,SMALL(IF("banana"=$A$1:$A$6,ROW($A$1:$A$6),9.99E+307 ),ROWS($A$1:$A1))),"")}

I have tried entering this into cell c1 and then clicking "ctrl, shift, enter" but it doesn't work. Can anyone help? perhaps with a different formula?

Perhaps the fault is in the text "9.99E+307"? Should this say something else? If so, what?

 

[Jerry Sullivan]

The following formula does not work:
{=IFERROR(INDEX($B$1:$B$6,SMALL(IF("banana"=$A$1:$A$6,ROW($A$1:$A$6),9.99E+307 ),ROWS($A$1:$A1))),"")}

I have tried entering this into cell c1 and then clicking "ctrl, shift, enter" but it doesn't work. Can anyone help? perhaps with a different formula?

Make sure you don't paste the curly brackets {} as part of the formula.
When you hit "ctrl, shift, enter", Excel will add those brackets.

If it still doesn't work, please identify what happens when you try to enter this.