Wanting to retrieve values from external workbook by searching name of column.

Hey guys,

So I have 2 workbooks which I am using. One workbook (lets call it signals) never changes it's format and contains a row of many signal names (example of a signal name : 'RF_Decoding_analysis::RF_Avg_SNR_RR'). This workbook is created from data exported from a program every time a new test is ran creating a CSV file. Under each signal I would like to retrieve the last value in the column. The other workbook (lets call it values) is where I would like to store the 'searched for' values of signals.

For the signal workbook, this will be overwritten every time a new test is ran as there will be different values under each signal. The main idea behind what I am trying to do is to allow the values workbook to auto-populate with the signal values each time a new test is done. A pathway or flow would look like such:

VALUES WORKBOOK - GO TO SIGNALS WORKBOOK - SEARCH FOR SIGNAL NAME - GO TO THAT COLUMN - RETRIEVE LAST VALUE - PLACE IN VALUES WORKBOOK - REPEAT FOR ALL SIGNAL NAMES AND VALUES.

I have tried multiple options such as 'VLOOKUP' and 'INDIRECT' but i find when I come to use 'VLOOKUP' I can't insert or dont know how to insert the address of the column that the signal name is in to get the value.

Any advice or solution would be greatly appreciated!

Thanks,

Ryan

Hey Ryan,

A workbook or screenshot for context would be helpful. But, try this.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Column 1 should contain information that identifies which row to look in.

the number 2,
the number 3,
the number 4
return the corresponding column number

Hope that helps.

What you need to do is make sure that the information is calling right.
In order to do that, you need to do 2 things.
First, make sure the correct cell is being looked up.

In this case, I changed Cell B1 in 'Signals' to match Cell C6 in 'Values'

Both read as "SNR_average_(dB)"

In the 'Values' Workbook, I used HLOOKUP and noting that you chose (for some ungodly reason) to go to column 'UX' I changed the row number to 'UX69'
Then I locked it with the $ '$UX$69'

Formula breakdown :

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Remember if you have a specific sheet to lookup, then you need to include it in your reference

Please Login or Register  to view this content.

I've managed to amend the formula as you have shown, however I need the formula to find the last or highest value in that column (preferably last) - instead at the moment it only returns the value I am looking for when the row the value is in is inserted into the formula - in the case above '2' which gives an output of '69'.

Therefore it will look up the signal name to get the column it's in and then take the last or highest value in that column which could be placed anywhere from row 2 to row 69.

Then instead, you might want to use this formula:

Solution

Please Login or Register  to view this content.

Be sure to change the column to the appropriate name.

Also, the LARGE function looks for the Nth largest number.
So 1, is the first largest. 2 is the second and so on.

if you used the formula as shown (with adjustments for column) $B,$C and so on
You should get results more akin to what you're looking for.

Tip
In the future, you might want to keep the names you are looking up on the same orientation for lookup purposes.
Then the existing formula would be something like:

Please Login or Register  to view this content.

Hope that helps.

I tried using the LARGE function on it's own with the column name inserted (SNR_average_dB) and its returning '#NAME?'. Do I have to combine HLOOKUP & LARGE or just use LARGE?

Thanks!

Alternatively, you would need to modify your database as illustrated in the SignalsMod2.csv
to search for the last item in a given list. There are formulas listed along the top row
This takes advantage of the MAX() Function and the VLOOKUP() and LARGE() functions.
This is where you would use

Please Login or Register  to view this content.