Dificulties to manage VLOOKUP

[Manuel Ferreira]

Good afternoon Gents

On the moment I’m working in a file and I’m struggling to manage VLOOKUP.
I’ve tried many times but the result is always #N/A.
Basically the file has three sheets:
- Operation list – is the data base sheet
- Piping System – the report that I want to fill with the data extracted from data base
- Cooling System – the report that I want to fill with the data extracted from data base

The “Piping System sheet” should:
- only show the data in which the lookup value [PIPING SYSTEM Module-1] on the data base sheet appears on columns N, O and P.
- has all info from column B to column M,

Please consider the attached file.

Can you please help me to figure out how can I fill each one of those reports with the right info?

My regards

[WideBoyDixon]

So I used a helper column in column N. In N3 I put this formula:

Please Login or Register  to view this content.

Then I hid column N. In B3 I put:

Please Login or Register  to view this content.

Which I then copied across and down. Finally, I changed column M to be formatted as a date. See attached for my output.

WBD

[Pepe Le Mokko]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

Note: As you are new I will add it for you.Please read forum rules which you might have missed

https://www.mrexcel.com/board/thread...ookup.1126363/

[Manuel Ferreira]

Good morning

Yes, you are right.
I will not do it again.

The link that I've missed to post is:

www .mrexcel.com/board/threads/dificulties-to-manage-vlookup.1126363/

[FDibbins]

Thanks for the link

I used a simpler approach but also used a helper (column Q in Op list - move it elsewhere or hide if needed)
Q2=COUNTIF($N$2:P2,"PIPING SYSTEM Module-1")
copied down. This will give a count, across all 3 columns, of your search criteria

Then to pull your data...
B3=INDEX('Operation List'!$B:$B,MATCH('Piping System'!$A3,'Operation List'!$Q:$Q,0))
copied down and across as needed - adjust formatting to suite

[Manuel Ferreira]

Hello

Thank you.
It works.
However I can't do it for the second sheet: COOLING SYSTEM Module-1
How do i do it?

My master file has 20 sheets.
Thus i do i do for each one of those sheets?

[WideBoyDixon]

See attached. You can copy any of the report sheets and put a new value into N1 to generate the report as necessary. When you're done, you can hide column N on the appropriate sheets.

WBD

[Manuel Ferreira]

Hello. Finally is working. Thank you for your good support. Appreciated

[Manuel Ferreira]

Hello WideBoxDixon

Yesterday I've managed to do all changes in my file as per your file example.
The changes made the file to work as i would like and based on that I've said the "finally isa workin".

Today morning when i opned the file nothing was working.
Instead of use column N I've dicided to usecell G1.
Can you please assit due my knowledge shortage?

Please find my file attached.

Regards

[WideBoyDixon]

I believe the problem is the last line of data on "Operation List" sheet which is a line of #N/A - that will prevent the formulas from working. I suggest you delete that line of data and wait a while ...

WBD

[Manuel Ferreira]

Hello WideBoxDixon

I've deleted as per your sugestion and also I've deleted line 1500 in which it is written the word END.
Now the info is on each sheet but still has some problem somewhere which doesn't alloud to get accurated info.
What do you recomend me to do?

Regards

[Manuel Ferreira]

Hello.
Finally is working.
Thank you for your good support.
Appreciated