Lists (Using INDEX vs. MATCH function)

  • Thread starter Thread starter schnett
  • Start date Start date
S

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
 
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.
 
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
 
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
 
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
Click to expand...
 
Back
Top