INDEX, FIND.... MATCH????

I posted this question once but the answer I got returned #NA. So I'm asking
for help again....

I need to search one column of worksheet 3 and find where the text "car"
occurs in c4:c44, for whichever cell in column c contains the text "car" (if
it is in c34), I need it to return the value contained in column b (the value
I would be looking for in this example would be b34.

This information will be pulled from sheet 3 of a worksheet and posted in
sheet 1.

=index(Sheet3!$b$4:$b$44,match("car",Sheet3!$c$4:$c$44,false))
This will return #NA if 'car' doesn't appear in the range c4:c44.
HTH. --Bruce

"Sonya" wrote:

> I posted this question once but the answer I got returned #NA. So I'm asking
> for help again....
>
> I need to search one column of worksheet 3 and find where the text "car"
> occurs in c4:c44, for whichever cell in column c contains the text "car" (if
> it is in c34), I need it to return the value contained in column b (the value
> I would be looking for in this example would be b34.
>
> This information will be pulled from sheet 3 of a worksheet and posted in
> sheet 1.
>

Thank You!

It worked like a charm. Could you tell me how to fix it so that if the value
I am searching for does not exist it will simply leave the cell blank ( not
return an answer) instead of returning #N/A?


"bpeltzer" wrote:

> =index(Sheet3!$b$4:$b$44,match("car",Sheet3!$c$4:$c$44,false))
> This will return #NA if 'car' doesn't appear in the range c4:c44.
> HTH. --Bruce
>
> "Sonya" wrote:
>
> > I posted this question once but the answer I got returned #NA. So I'm asking
> > for help again....
> >
> > I need to search one column of worksheet 3 and find where the text "car"
> > occurs in c4:c44, for whichever cell in column c contains the text "car" (if
> > it is in c34), I need it to return the value contained in column b (the value
> > I would be looking for in this example would be b34.
> >
> > This information will be pulled from sheet 3 of a worksheet and posted in
> > sheet 1.
> >

You'd put the function you've got so far inside an IF that test for the NA:

=if(isna(match("car",Sheet3!$c$4:$c$44,false)),"",index(Sheet3!$b$4:$b$44,match("car",Sheet3!$c$4:$c$44,false))).

In words, if the match returns NA, leave the cell blank. Otherwise,
execution the index/match functions.
--Bruce

"Sonya" wrote:

> Thank You!
>
> It worked like a charm. Could you tell me how to fix it so that if the value
> I am searching for does not exist it will simply leave the cell blank ( not
> return an answer) instead of returning #N/A?
>
>
> "bpeltzer" wrote:
>
> > =index(Sheet3!$b$4:$b$44,match("car",Sheet3!$c$4:$c$44,false))
> > This will return #NA if 'car' doesn't appear in the range c4:c44.
> > HTH. --Bruce
> >
> > "Sonya" wrote:
> >
> > > I posted this question once but the answer I got returned #NA. So I'm asking
> > > for help again....
> > >
> > > I need to search one column of worksheet 3 and find where the text "car"
> > > occurs in c4:c44, for whichever cell in column c contains the text "car" (if
> > > it is in c34), I need it to return the value contained in column b (the value
> > > I would be looking for in this example would be b34.
> > >
> > > This information will be pulled from sheet 3 of a worksheet and posted in
> > > sheet 1.
> > >