Finding Lowest Number in a range of numbers.

stevenson08 · #1 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 · #2 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 · #3 11-11-2008, 07:51 AM

Quote:

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.

Code:

M2    =SMALL(C2:K2,1)
O2    =SMALL(C2:K2,2)
Q2    =SMALL(C2:K2,3)

Copy down.

**Andy Pope** · #4 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 · #5 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 · #6 11-11-2008, 08:08 AM

Quote:

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.

**Andy Pope** · #7 11-11-2008, 08:10 AM

see attached

stevenson08 · #8 11-11-2008, 08:19 AM

excellent andy thank you i will try that and come back to you.

Thanks

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)