Getting last row value with INDEX MATCH MATCH

Originally Posted by dangelor

User-defined function...

Please Login or Register  to view this content.

Oh wow, thank you for replying so fast, and this does exactly what I wanted! I should really get into VBA as well, seems to open a whole new world of possibilities.

Edit: Ah, there is one more problem. If the range where I want to search is on a different worksheet, how does the function change? Let's say I have a named range which goes to exportedvalues1.xlsx, 'Data'-tab and range would be for example A5:K20? Workbook name is a variable but tab name and the range is generally static.

Also, the row where to search is not always row nr. 1.

Formula solution:

Assum Indentifier condition in H8

H9:

Please Login or Register  to view this content.

I've been waiting for you to upload and still haven't waited until now
Because I want to confirm the actual location of your attachment cell area

Try this formula (Array formula)

HTML Code: 

Please try

=LOOKUP(9^9,INDEX(C:K,,MATCH(N13,B1:J1,)))

Thank you everyone for your answers

Originally Posted by dangelor

See if this is what you need...

Please Login or Register  to view this content.

Getting very close here, does exactly what is needed, but is it really not possible to create a function which does exactly the same thing? This macro of yours allows me to do searches one at a time, but I would need this data to update automatically, and so that I can drag'n'fill formula in a table, which contains file name variable in another cell telling the macro in which workbook to search in (as mentioned before, tab name is always same, content order is generally the same but may vary occasionally, only workbook name and path is a variable)..

But hey, superb work here, thank you! If you have any more ideas towards making something like I described, I would appreciate that a lot.

E: Here is an attachment showing how I would need it to be

Originally Posted by dangelor

Like you, I started asking for help... Good luck!

Hey, one more slight issue. Things changed a bit and I have to search values from 2 different tabs, everything is exactly the same but tab name is different (however the same workbook), and the function to execute is exactly the same. I can obviously copy-paste this as another VBA-script and call it after the first one has completed, but in that case it takes 5 minutes for this script to complete.

I'm guessing that could be halved by opening the workbooks only once and getting values from 2 different tabs, but I can't write that kind of code. Does that sound like something you could maybe do? So, exactly the same code as you posted earlier but somehow look from 2 different tabs with same search terms..

Thanks a bunch!

Edit: I managed to achieve this but I'm not sure if this is a efficient way to run this script. Takes at the moment 4 min 30 sec to open 27 workbooks and get values. Could this be shortened somehow? Dangelor..?

Code:

Please Login or Register  to view this content.