Lists (Using INDEX vs. MATCH function)

[schnett]

In the attachment I'd like to write a formula in cell G9,G10,G13, an
G14 to search through the list in column B and C and find the leas
positive values and the least negative values. Essentially the value
surrounding "0". Then I would like to write a formula in F9,F10,F13,F1
to identify the respective rows in column A that were found i
G9,G10,G13,G14. I have tried to manipulate INDEX and MATCH functio
unsuccessfully. It maybe possible that I'm using the wrong function
all together.

Can you lend a helping hand

Attachment filename: temp.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=62322

 

[macropod]

Hi,

In F9, F10, F13 and F14, you could use:
=INDEX(A$2:A$41,MATCH(G9,B$2:B$41,0))
=INDEX(A$2:A$41,MATCH(G10,B$2:B$41,0))
=INDEX(A$2:A$41,MATCH(G13,C$2:C$41,0))
=INDEX(A$2:A$41,MATCH(G14,C$2:C$41,0))
respectively.

In G9, G10, G13 and G14 you could use:
=MAX(IF((B$2:B$41<0),(B$2:B$41),-999))
=MIN(IF((B$2:B$41>0),(B$2:B$41),999))
=MAX(IF((C$2:C$41<0),(C$2:C$41),-999))
=MIN(IF((C$2:C$41>0),(C$2:C$41),999))
respectively, all array-entered (ie with Ctrl-Shift-Enter instead of just
Enter).

Cheers
PS: the 999s in the last 4 formulae can be any value larger than the outer
limits of possible values.

 

[schnett]

In the attachment, I replaced each searching cell with your formula. Th
resulting value in G9 for instance seems seems to be FALSE in the I
function. Although, if you click on the formula function in the G9 cel
it has a resulting value of "-3.9" which is exactly what I want. But i
doesn't show up in the cell.

Any assistance would be helpful in column G

Attachment filename: temp.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=62355

 

[macropod]

Hi,

I had a look at your updated workbook, and it has a number of problems,
including:
1. You haven't used exactly the same formulae that I posted (eg all your
formulae in column F have extra + signs and in G10 you have extra + and -
signs)
2. You didn't array-enter the formulae in column G, which is essential
3. Your workbook is set to manual calculation

Cheers

 

[schnett]

Thank you for telling me those tips again, my speadsheet work
beautifully now.

Ty

*Hi,

I had a look at your updated workbook, and it has a number o
problems,
including:
1. You haven't used exactly the same formulae that I posted (eg al
your
formulae in column F have extra + signs and in G10 you have extra
and -
signs)
2. You didn't array-enter the formulae in column G, which i
essential
3. Your workbook is set to manual calculation

Cheers