Index Match

[ststern45]

Need help with a formula.

I have 2 columns

Column A1:A100 contain duplicate values ranging from 1 through 19

Column B1:B100 contain various values and some duplicate values based on the value in column A. See below.

My question is this:

1st I would like to create a formula that calculates the lowest to highest values from column B that correspond to the values in column A

For example:

A1 = 19 B1 = 4
A2 = 19 B2 = 4
A3 = 19 B3 = 5
A4 = 19 B4 = 6

So for the Value 19 in column A is it possible to calculate the lowest value, which is a 4, then 5, then 6. Notice the 4 appears twice.

Part 2

Count the number of times a 4 appears. In the above example, the total would be 2. 5 = 1, and 6 = 1.

Note: cell column A1: through A100 contain values ranging from 1 through 19. I used the example above with a 19 to avoid confusion.

Thanks in advance

 

[Aladin Akyurek]

Have a followup request if I may.

Aladin's formula works to perfection.

The range of possible calculations are 0 through 18.

I'll use the example of 18.

These are my results using Aladin's formula

Cell C3 = 6
Cell C4 = 7
Cell C5 = 8
Cell C6 = 9
Cell C7 = 10
Cell C8 = 11
Cell C9 = 12
Cell C10 = 13
Cell C11 = 14
Cell C12 = 15

Ok my question is this.
In cell range A3 through A21 I have the numbers 0 through 18.

How can I match the value in cell A9 to the number 6 from C3?

So if the 1st value calculated is a 6 how can I have it leave blank cells in cell range C3 through C8 and in cell C9 (6) have it show the 6?

Cell A3 = 0
Cell A4 = 1
Cell A5 = 2
Cell A6 = 3
Cell A7 = 4
Cell A8 = 5
Cell A9 = 6
Cell A10 = 7
Cell A11 = 8
Cell A12 = 9
Cell A13 = 10
Cell A14 = 11
Cell A15 = 12
Cell A16 = 13
Cell A17 = 14
Cell A18 = 15
Cell A19 = 16
Cell A21 = 17
Cell A21 = 18

So the results would look like this:

C3 = Blank
C4 = Blank
C5 = Blank
C6 = Blank
C7 = Blank
C8 = Blank
C9 = 6 Note: matches cell A9
C10 = 7 Note: matches cell A10
C11 = 8
C12 = 9
C13 = 10
C14 = 11
C15 = 12
C16 = 13
C17 = 14
C18 = 15
C19 = Blank
C20 = Blank
C21 = BlanK

Thanks

Is this what is required?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

3​

[/td][td]

0​

[/td][td][/td][td]

6​

[/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]

1​

[/td][td][/td][td]

7​

[/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]

2​

[/td][td][/td][td]

8​

[/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]

3​

[/td][td][/td][td]

9​

[/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]

4​

[/td][td][/td][td]

10​

[/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]

5​

[/td][td][/td][td]

11​

[/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]

6​

[/td][td][/td][td]

12​

[/td][td][/td][td]

6​

[/td][/tr]
[tr][td]

10​

[/td][td]

7​

[/td][td][/td][td]

13​

[/td][td][/td][td]

7​

[/td][/tr]
[tr][td]

11​

[/td][td]

8​

[/td][td][/td][td]

14​

[/td][td][/td][td]

8​

[/td][/tr]
[tr][td]

12​

[/td][td]

9​

[/td][td][/td][td]

15​

[/td][td][/td][td]

9​

[/td][/tr]
[tr][td]

13​

[/td][td]

10​

[/td][td][/td][td][/td][td][/td][td]

10​

[/td][/tr]
[tr][td]

14​

[/td][td]

11​

[/td][td][/td][td][/td][td][/td][td]

11​

[/td][/tr]
[tr][td]

15​

[/td][td]

12​

[/td][td][/td][td][/td][td][/td][td]

12​

[/td][/tr]
[tr][td]

16​

[/td][td]

13​

[/td][td][/td][td][/td][td][/td][td]

13​

[/td][/tr]
[tr][td]

17​

[/td][td]

14​

[/td][td][/td][td][/td][td][/td][td]

14​

[/td][/tr]
[tr][td]

18​

[/td][td]

15​

[/td][td][/td][td][/td][td][/td][td]

15​

[/td][/tr]
[tr][td]

19​

[/td][td]

16​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

20​

[/td][td]

17​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

21​

[/td][td]

18​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In E3 enter and copy down:

=IF(ISNUMBER(MATCH(A3,$C$3:$C$12,0)),A3,"")

 

[ststern45]

Yes. Thank you.

I know the original formula you posted does not match.
=IFERROR(SMALL(IF(FREQUENCY(IF(1-($B$1:$B$100=""),IF($A$1:$A$100=E$1,MATCH($B$1:$B$100,$B$1:$B$100,0))),ROW($B$1:$B$100)-ROW($B$1)+1),$B$1:$B$100),ROWS($E$3:E3)),"")

Your last post above I wanted to ask instead of the 1st value "6" in column C3 is there a way using the formula above to place the value of "6" in cell C9 instead of cell C3?

Thank you.

 

[Aladin Akyurek]

Yes. Thank you.

[...]
Your last post above I wanted to ask instead of the 1st value "6" in column C3 is there a way using the formula above to place the value of "6" in cell C9 instead of cell C3?

[...]

A3:A21 and C3:C12 are input as you specified. The set up of post #11 uses them to create E3:E21.

Note that a formula cannot reorder C3:C21 such that it looks like E3:E21. If that's your intention, someone into VBA should take up that request.