SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-3071055%22%20slang%3D%22en-US%22%3EComparing%20and%20getting%20a%20result%20in%20a%20column%20based%20on%20another%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3071055%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20All%2C%3C%2FP%3E%3CP%3EHave%20been%20trying%20to%20get%20the%20output%20in%20the%20file%20by%20using%20Vlookup%20but%20it%20is%20not%20working%20hence%20need%20help%20to%20complete%20the%20task.%3C%2FP%3E%3CP%3EHave%20a%20data%20set%20where%20there%20are%202%20columns%20which%20are%20the%20top%20and%20bottom%20of%20the%20range%20and%20want%20to%20get%20the%20Aquifer%20name%20from%20another%20table%20which%20matches%20to%20this%20dataset%20and%20the%20each%20row%20returns%20the%20value%20from%20the%20other%20table.%20Now%20even%20if%20there%20are%20multiple%20names%20in%20between%20the%20range%20all%20should%20appear%20there.%3C%2FP%3E%3CP%3EAttaching%20a%20a%20file%20for%20the%20reference.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3071055%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Contributor

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.  

10 Replies

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_ 

Just tried the Vlookup formula as you suggested and have attached the sheet please do tell if I'm doing it wrong.

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

Now the Column A and B is the range which should compare it with the Column F and G and return the Value of Column E in to the Column C.
In short the range of top and bottom should match and return the text from Column Aquifer to the matching rows and fill it across the data.

@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.

@ 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.
best response confirmed by Aditya Jadhav (Contributor)
Solution

@Aditya Jadhav See attached. Would that be it?

Riny_van_Eekelen_0-1643101917983.png

 

Yes that is it. This is what is needed. If you don't mind can you please guide me step by step how you did this as I'm getting confused with the Sheet. Just cannot relate what all you did to get the final outcome.

@Aditya Jadhav Well, PQ is very powerful, but not always very easy. First I connect to the two tables. I call them TopBottom and Aqui. From the Aqui table I extract a list of only the aquifer names "AquiList", an I add a few extra columns to it that are needed later (Source and Index). By the way, I also added an Index column to the TopBottom table so that you can sort the outcome in the same order as your original table.

 

The Append query joins the two tables, filters, duplicates one column. Then it sorts Geo_Top and fills down the Aquifer column. Then it sorts the Geo_Bottom column and fills up the Aquifer - Copy column.

 

What comes next is a series of steps to determine the first and the last Aquifer code in the range (top to bottom) and then select all of the ones in between from the AquiList with the List.Range function. This part is probably subject to improvement by the hands of a real PQ master. 

 

The last three steps are just to cleaning up a bit.

@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)
 ) )