Jan 24 2022 10:27 PM
Jan 24 2022 10:27 PM
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.
Jan 24 2022 10:46 PM - edited Jan 24 2022 10:52 PM
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
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
Jan 24 2022 11:02 PM
Just tried the Vlookup formula as you suggested and have attached the sheet please do tell if I'm doing it wrong.
Jan 24 2022 11:09 PM - edited Jan 24 2022 11:15 PM
What lookup value are yo
u looking for
Table_array - where can I find them
Column index - what column number should I return
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
Jan 24 2022 11:17 PM
Jan 24 2022 11:45 PM
@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
Jan 25 2022 12:15 AM
Jan 25 2022 01:12 AMSolution
@Aditya Jadhav See attached. Would that be it?
Jan 25 2022 03:36 AM
Jan 25 2022 04:20 AM
@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.
Jan 25 2022 12:56 PM
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) ) )