CONCATENATE returning #N/A from VLOOKUP

[paul_lowry]

I have been tasked with creating a spreadsheet for monitoring consultancy projects in my work.
There are 10 consultants but they don’t tender for every job we put out. But I have 2 issues that I can’t resolve.

1) When a consultant does submit a tender I add their name with a dropdown and then include a link to their tender and a % score.
What I am trying to do is have a box that will look at the project number and CONCATENATE all submitted tenders.
But when I do this I get a #N/A response (the attached spreadsheet might make it easier to understand)

2) Secondly if it is possible I would like to be able to see how many consultants tendered for a certain project again using a dropdown
And then using another dropdown underneath calculate in what position this consultant came in the tender process
i.e Project 01 – Consultant 02 – Came 4th out of 5 tenders submitted

This is a little tricky to explain so I have attached a spreadsheet with notes.
I would greatly appreciate anyone that could help with either of these problems.

[Glenn Kennedy]

I don't see the point of the big conatenation/merged cell. It might be working now, though. If you really need it (why can't you just look at B2:c11, instead - they contain exactly the same information!!)

Try to avoid the use of meged cells. They're the work of Satan!!

The other requests were easy, once I killed (some of) your merged cells.

[paul_lowry]

I don't see the point of the big conatenation/merged cell. It might be working now, though. If you really need it (why can't you just look at B2:c11, instead - they contain exactly the same information!!)

Try to avoid the use of meged cells. They're the work of Satan!!

The other requests were easy, once I killed (some of) your merged cells.

Thank you so much Glenn.

For the scoring position, is there a way to calculate which tender came 1st, 2nd or 3rd etc. rather than show the tender score?

Appreciate your help

[Glenn Kennedy]

Try this...

[paul_lowry]

Try this...

Fantastic Glenn

Many thanks