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.