How to Bring back the Value of the top of the column

Hello Guys

I have been struggle myself trying to do a sort of (offset, Index or whatever)
What I basically need to do is return the value that is in the top of the column
Like I was doing a kind of (un-offset)


In the spreadsheet attached there is a list of people and jobs that they were on
When I chose a job number I need to bring back who was on that job, there is always more than one person in the same job

I attached the spreadsheet

Can anybody help me, please?

in B13 and copy down

=IFERROR(INDEX($B$3:$H$3,,MATCH($B$12,$B4:$H4,0)),"")

However if the job is not on consecutive days you will get a blank "name"

e.g. change Job123 to Job124 on any day.

Hello, that would be a problem cos the Jobs are not on consecutive days
My real spreadsheet is much bigger, I Basically have 197 names and one full year of dates
But this will definitely help me, thanks
Let’s see if somebody else knows anything
Maybe insert a Macro to remove the blank Lines

Put this in column C13 to remove blanks

=IFERROR(IF(ROWS(C$13:C13)>COUNTA($B$13:$B$200),"",INDEX($B$13:$B$200,SMALL(IF(B$13:B$200<>"",ROW(B$13:B$200)-12),ROWS(C$13:C13)))),"")

Enter with Ctrl+Shift+enter

Hi,
Please check the attachment.

This questions wasn't originally posted looking for a VBA solution, but I have one for you if you can't find a formula solution that fits your needs.

Insert a module in your workbook and paste the following code.

Please Login or Register  to view this content.

Then you can use the formula in cell B13 as follows:

Please Login or Register  to view this content.

The first section of this custom function is where you put the job code you're looking to match.
The second section is the entire range with the NAMES IN THE FIRST SELECTED ROW.
The third section shows which matching employee you want returned (put a 1 and it will return the first matching employee, put a 2 and it will return the second matching employee, etc....)

I hope you find a solution that doesn't require a custom function, but you've got this as a backup at least!

Hello ThirdFret

It worked in my big spreadsheet, I am not really good at VBA, thanks

After getting the names I need to know how many days the guys worked in each month

I want to enter with the month in B10
And I’d like to have the answer in C13, C14 ….

If I chose Job 123
Paul worked 2 days
Alejandro worked 0 days cos he worked in September
Colin worked 1 day
Denis worked 1 day


Do you know this question as well?

thanks

base on ThirdFret file:

date in B10: 01/08/2015

in C13 ...

=IF(B13="","",SUMPRODUCT(($B$3:$H$3=$B13)*($B$4:$H$7=$B$12)*(MONTH($A$4:$A$7)=MONTH($B$10))))

Copy down

The following works well for me. Keep in mind this is an ARRAY FORMULA WHICH IS ENTERED BY PRESSING CTRL+SHFT+ENTER, NOT JUST ENTER.

Please Login or Register  to view this content.

I've attached the file again, for reference, and included a larger test data set.

Hope this helps.

Thanks very much
Can I change this to solved ?

If you feel we've adequately answered your question I think you should! (just under Thread Tools at the top)

Cheers!