Vlookup - Look for a value in a filtered list

Gemini74

New member
Joined
Jul 26, 2019
Messages
2
Reaction score
0
Points
0
Excel Version(s)
Microsoft 365 proplus
Dear all,

I would like to lookup a value, in a list that can change because it is filtered thanks to the automatic filter.

Let's say that I want to look for the value contained in the cell A2400, in the range $A$2:$A$43, but the visible lines in that range can change due to filtering (Only 1 line remains in general).

The practical case is the following: if the user filters to choose 1 project in the range $A$2:$A$43, the proper currency should be displayed in cell A2400, as each project has a different currency.

I went through the forum but could not find anything that works for my case.

Thanks a lot for your help
Gemini
 
Please supply a workbook with this scenario. It will answer many questions that people wanting to help will have without having to guess (probably incorrectly).
eg. What decides what the 'proper' currency is? Is the currency displayed via the cell formatting? Are you wantimg to ignore hidden rows in the search - or the opposite? You're searching within range A2:A43 for whatever's in cell A2400, yet you want that cell to display the 'proper' currency! There's plenty more.
Sounds like you might need VBA.
 
Please supply a workbook with this scenario. It will answer many questions that people wanting to help will have without having to guess (probably incorrectly).
eg. What decides what the 'proper' currency is? Is the currency displayed via the cell formatting? Are you wantimg to ignore hidden rows in the search - or the opposite? You're searching within range A2:A43 for whatever's in cell A2400, yet you want that cell to display the 'proper' currency! There's plenty more.
Sounds like you might need VBA.

Thanks a lot for your reply

Please find attached the worksheet, let me know in case you have questions.

thank you
Ivan
 

Attachments

  • Test.xlsx
    9.6 KB · Views: 18
CROSS POST

This question has been posted in at least one other forum.
Do not post any further responses in this thread until a link has been provided to these cross posts.

Whilst we would rather you didn't cross-post, we know that there may be a valid reason for doing so. All we ask is that you provide the links in your original post to your question in the other forum(s). Read this to understand why.

This forum is full of volunteers that give up their own time to help others, something that should be respected and not taken for granted. It’s never nice for someone to find out that a problem they have spent time solving for you has already been answered somewhere else without them knowing, and so we ask you to make it clear that you have also asked elsewhere.
Please provide the link(s). If you are unable to do so, tell us where else you have posted this query.
 
In cell B13, array-enter (use Ctrl+Shift+Enter to commit the formula to the sheet, not just Enter):
=INDEX(B1:B7,MIN(IF(SUBTOTAL(103,OFFSET(B2,ROW(B2:B7)-ROW(B2),0))=1,ROW(B2:B7))))
in your locale: =INDEX(B1:B7;MIN(IF(SUBTOTAL(103;OFFSET(B2;ROW(B2:B7)-ROW(B2);0))=1;ROW(B2:B7))))
and it will show the first visible cell value in column B (whether the rows are hidden manually or via a filter, or both).
 

Attachments

  • ExcelGuru10132Test.xlsx
    9.8 KB · Views: 0
Last edited:
I have a solution for you, with a file with it working, but will not post it until you have complied with AliGW's requirement.
 
Back
Top