View original (French)

Value of the cell above

Solved
Amc0169 Posted messages 2 Status Member -  
Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   -
Hello,

I'm looking for a formula that would allow me to search for and extract information located in other cells.

I have a first table:

Company 1 / Company 2 / Company 3 /
Name of Company 1 / Name of Company 2 / Name of Company 3 /
Price #1 / Price #2 / Price #3 /

Then I have a second table:

Ranking / Price / Company
1 / Price #2 / Empty cell
2 / Price #1 / Empty cell
3 / Price #3 / Empty cell

In the "empty cell," I would like a formula that retrieves the name of the company corresponding to the price.

I am attaching a link to an Excel file for reference.

https://www.cjoint.com/?DFxneGwPlzY

Thank you.

3 answers

  1.
    michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello
    =INDEX(A$2:I$2,MATCH(C8,A$3:I$3,0))
    to drag down

    or as a precaution (wrong entry)
    IF(COUNTIF(A$3:I3,C8)=0,"Unknown amount",INDEX(A$2:I$2,MATCH(C8,A$3:I$3,0)))

    --
    Michel
    1
    1. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      Hi Michel

      Alright, you have a minute to spare!... but I think there's a missing semicolon in your formula? before EQUIV because it’s about detecting the column, not the row.

      That said, the input cannot be incorrect since the list of amounts is established based on row 2
      which also allows for a direct formula to be written without the list of amounts in C:

      =INDEX($A$2:$I$2;;EQUIV(SMALL($A$3:$I$3,ROW()-7);$A$3:$I$3;0))

      (ROW()-7 is only valid if we start in row 8)

      Best regards
      0
  2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello

    in B8, to be pulled down:

    =INDEX($A$2:$I$2;;MATCH(C8;$A$3:$I$3;0))

    note, if there are two identical prices, the formula only returns the first one

    best regards

    --
    To err is human, to persist is diabolical.
    1
    1. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
       
      Hello everyone, Michel and Vaucluse ;)
      To address the case of 2 identical prices, I would propose a 4th line with in A4 the formula: =RANK(A3;$A$3:$I$3;1)+COUNTIF($A$3:A3;A3)-1 stretched up to I4

      and in B8 the formula: =INDEX($A$2:$I$2;;MATCH(ROWS($B$8:B8);$A$4:$I$4;0)), to be stretched

      Best regards
      0
  3. Amc0169 Posted messages 2 Status Member
     
    Thank you for your help, it solves my problem!

    Best regards
    0
    1. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      ok, we move on to resolved
      Safe travels
      0
    2. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
       
      Re,

      To my friend Vaucluse, it’s been a while since we’ve had a bump together :o)
      I think you’re missing a semicolon in your formula? before EQUIV because it’s about detecting the column, not the row.
      No, if there’s only one row or one column, it’s not necessary.
      0
    3. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      Sure, thanks for the info, have a good evening
      best regards
      0