Excel 2007 : Finding Lowest Number in a range of numbers.

**stevenson08** · 11-11-2008, 07:15 AM

Hello,

I have a spreadsheet with 11 columns and 11 rows. The first column has a list of part numbers and the head row has a list of account numbers. For each account there is a price for each part number. I need to find for each part the 3 accounts with the lowest price that are cheaper than the list price. I am new to excel and I am extremely new to office 2007. Any help will be greatly apprecitated.

Can anyone help?

Thanks

Stevenson08

**stevenson08** · 11-11-2008, 07:40 AM

Hello,

I think i have managed to solve my dilema using HLookup, However now i cannot get the row number in the formula to increment decending. For example:

=HLOOKUP(M2,B2:K12,11,FALSE)
=HLOOKUP(M2,B2:K12,10,FALSE)

Any ideas?

Thanks

**JBeaucaire** · 11-11-2008, 07:51 AM

Originally Posted by stevenson08

Hello,

I think i have managed to solve my dilema using HLookup, However now i cannot get the row number in the formula to increment decending. For example:

=HLOOKUP(M2,B2:K12,11,FALSE)
=HLOOKUP(M2,B2:K12,10,FALSE)

Any ideas?

Thanks

For PART 1, the function you want is SMALL.

Please Login or Register  to view this content.

Copy down.

**Andy Pope** · 11-11-2008, 07:58 AM

You could try this array formula in M2, use CTRL+SHIFT+ENTER

=SMALL(IF($C2:$K2<$B2,$C2:$K2),1)

when copying to O2 and Q2 change the second argument in SMALL function from 1 to 2 and 3.

and in N2

=INDEX($C$1:$K$1,MATCH(M2,$C2:$K2,0))

Currently neither formula check for errors such as no prices being cheaper than list.

**stevenson08** · 11-11-2008, 08:06 AM

I have got the small forumla to work, i am now looking at hlookup but i cannot seem to get the row number that it brings the data back from does not decrease if you understand my meaning.

Thanks

**JBeaucaire** · 11-11-2008, 08:08 AM

Originally Posted by stevenson08

I have got the small forumla to work, i am now looking at hlookup but i cannot seem to get the row number that it brings the data back from does not decrease if you understand my meaning.

Thanks

No, but if it's column O you're filling in, that INDEX/MATCH formula above is the one you want to use.