find two large numbers!

[stewart08]

Dear excelers,

how can I create a function that can find two large/small numbers from a list of numbers i.e.

numbers
125101845107

need result: 87

below is my nearest function, but still does not work! please how is this do...thankx

=IF((LARGE(I101:AV101,1))*OR(LARGE(I101:AV101,2))=AI101,6,"")

[darkyam]

I take it from the range you have below that all your numbers are in different cells. If so, use =Large(I101:AV101,1) in one cell and =Large(I101:AV101,2) in the next. Or, to combine them into one cell, =Large(I101:AV101,1)&Large(I101:AV101,2)

[stewart08]

darkyam thankx for the quick reply.

I just tested this function:

=IF(LARGE(I101:AV101,1)&LARGE(I101:AV101,2)=AI101,0,"")

seem to still not work

it needs to find the large1 or large2 in a cell array...please help.

[ChemistB]

Enter Darkyam's equation just as he wrote it. Why are you changing it into an IF statement?

ChemistB

[stewart08]

may be I donot make it clear, once again:

row1: 0123456789 (refer nums)
row2: 1281006372 (sample nums)
row3: 2 8 (function per cell)

need finally result: 2,8

row3 function per cell needs to:
1) find largest or second largest digits in row2 = 87
2) then 87 is refers to row1 = 28

i hope it is clear now

[stewart08]

at the simplest level this is what I am trying to do:

seach a value via two other values i.e.

if 2 = 2 or 3! then use value "8"

or

need to use two function criteria (largest or second largest) in one function argument!

is this possible...any help would be greatful

[ChemistB]

Ahh, the lightbulb comes on (I think)

I assume the digits are in individual cells (see attached file)

I split the formula between A3 and B3, you can combine it with & if you like. They are:

Please Login or Register  to view this content.

Is this what you need?

ChemistB

[stewart08]

ChemistB thankx
this really helped. would you know how make this work with matching numbers i.e.

0123456789
0030003000 = 2,6

your function only returns = 2,2!

[ChemistB]

Okay, the only way I could get it to match unique numbers was to do a fancy "Ranking" first to create a unique number list. I did this in rows 3 and 4 (you'd only need 1 but I did it two ways so you have a choice). You could hide that row or even put the font as white so it doesn't show if that suits you.

Row 3 (example for A3) ranking from smallest to largest

Please Login or Register  to view this content.

Row 4 (e.g. A4) ranking from largest to smallest

Please Login or Register  to view this content.

Formulas in A6,B6 using row 3

Please Login or Register  to view this content.

This will give preference with matching numbers to the one on the right thus the result come out as 2, 6.

Please Login or Register  to view this content.

This set gives the results as 6, 2.

I could not combine everything into 1 formula (removing rows 3 and 4). If that is what you need, I'd suggest starting a new thread with this as your new starting point and see if others can help. Good luck!

ChemistB