Thanks so much for your response, Shail. I tried the modified formula
and it worked for me -- on the first cell.

Here is what I modified it to:
=INDEX('1 of 20'!$A$11:'1 of 20'!$F$30,MATCH("X",'1 of 20'!$F$11:'1 of
20'!$F$30,0),1)

1 of 20 - worksheet with data
A11-F30 - range with data
F11-F30 - row with data that is being evaluated or Value X

For the first cell, it worked. But how do I get it to work in a report?
It only printed the first cell and wouldn't search any further. (C&Ping
gets me the same result as the first.)

Is there something that I am doing wrong in my modification of the
formula?

Holli



shail wrote:
Hi HBuck,

VLOOKUP will certainly not work in this case as VLOOKUP is ment to
LOOKUP towards the right side of any range whereas in your case it is
LOOKUPing both right and left sides of your data range.
So in your case you need INDEX & MATCH functions as below

=INDEX($A$1:$F$5,MATCH("X",$D$1:$D$5,0),1)

The " 1 " at the end is the column number, change it according to the
need of your columns, say if you need the values from leftmost column
of your range then enter 1, if 2nd column from Left, then enter 2, so
on.

Hope this works for you.


thanks

Shail



HBuck wrote:
Good morning,

I am working on a formula that is giving me more trouble than I can
imagine and I would appreciate someone's help.

I have a spreadsheet that has information similar to this:

A B C D E
F
No. Question Y N N/A Recommendation
1 1.1 What is it? x
2 2.1 Who is it? x Figure out
who it is.
3 3.1 Where is it? x
4 4.1 When is it? x Figure out
when it is.

What I need is a function that will search each row and when it
encounters an 'X' in column D, I want to print the following fields on
a separate sheet: A, B, and F.

The Report would look like this:

A B C
No. Question Recommendation
1 2.1 Who is it? Figure out who it is.
2 4.1 When is it? Figure out when it is.

Can anyone shed some light on this? I have found other respondes on the
boards about doing a LOOKUP on rows and returning certain values, but I
haven't found anything that helps me with the above problem.

Any help would be appreciated!
~H