Forum Discussion

Aditya Jadhav's avatar
Aditya Jadhav
Brass Contributor
Jan 25, 2022
Solved

Comparing and getting a result in a column based on another table

Dear All,

Have been trying to get the output in the file by using Vlookup but it is not working hence need help to complete the task.

Have a data set where there are 2 columns which are the top and bottom of the range and want to get the Aquifer name from another table which matches to this dataset and the each row returns the value from the other table. Now even if there are multiple names in between the range all should appear there.

Attaching a a file for the reference.  

excel
Formulas and Functions

10 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 25, 2022

    Aditya Jadhav 

    As variant that could be

    =IF(
    [@[Geo_Top]] = "", "",
    TEXTJOIN(", ", 1,
        XLOOKUP( --[@[Geo_Top]],
                 Aqui[Geo_Top],
                 Aqui[Aquifers],
                 INDEX(Aqui[Aquifers], 1 ),
                 1 ):
        XLOOKUP( --[@[Geo_Bottom]],
                 Aqui[Geo_Bottom],
                 Aqui[Aquifers],
                 INDEX(Aqui[Aquifers], ROWS(Aqui) ),
                 -1)
 ) )
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jan 25, 2022

    Aditya Jadhav I notice you started using PQ but I could not really see what you were doing as you connected to a file on your local system. Tried to replicate what I think you want to achieve. The end result is in columns K:M where column M contains the references to the highest and lowest Aquifier code for that particular range

     

    See attached.

    • Aditya Jadhav's avatar
      Aditya Jadhav
      Brass Contributor
      Jan 25, 2022
      @ Riny_van_Eekelen There is a problem in the returned values in Aqui the resulted values are not complete. Some are missing as the range takes in the resulted first row check that the value is Aq-XIII, Aq-XV but then the Aq-XIV is missing how do we tackle that.
  • Donald_Genes_'s avatar
    Donald_Genes_
    Brass Contributor
    Jan 25, 2022

    You broke the vlookup rules
    1. You can use Xlookup
    2ndly, you should swap the Column that Contains AqX11 to be the first column on the left (except you want to use index and Match

    Like this
    Original data
    COL E | Col F | Col G
    After switching it to
    ColG | Col E | Col F

    Then vlookup will be able to extract the data

    Do it now and let me know how it goes

      • Donald_Genes_'s avatar
        Donald_Genes_
        Brass Contributor
        Jan 25, 2022

        Question is,
        What lookup value are yo

        Spoiler
         

        u looking for
        Table_array - where can I find them

        Column index - what column number should I return

        False

        Now,I your case you are looking for A2 which is a number
        Of course Vlookup would return NOt Available because there was no matching value

        I am using Excel mobile app to check your function..

        So the me what exactly you are looking for again?

         

         

        Anyways let me turn on my laptop and do it for you

Resources