returning values from an unsorted list.

[Gef]

In Office 2000, I have 4 sheets - week 1 to 4. I want to search a particular
column for specific text and return a value from the column to the left of
the text.

Any ideas?

Thanks in advance.

 

[Dave Peterson]

=index('week 1'!g:g,match("somevalue",'week 1'!z:z,0))

Debra Dalgleish has some nice instructions:
http://contextures.com/xlFunctions03.html

 

[Gef]

If the column I want to search is I2:I300 and the cell I want returned is in
H2:H300, where would I insert them in the formula. I've tried I2:I300 as g:g
and H2:H300 as z:z, and vice versa with no success... Help!!!

 

[Dave Peterson]

Whats the name of the worksheet that holds the ranges?
What's the address of the cell that contains the value that you want to match?
Is the cell with the formula on a different different worksheet than either of
these?

Assuming that the table (h2:I300) is on sheet2 and the cell with the value is A1
of sheet1 and the cell with the formula is also on Sheet1:

=index(sheet2!h2:h300,match(a1,sheet2!i2:i300,0))

If this doesn't help, take a look at Debra's site. She has lots of info there.