Need Help with Lookup Formula

[gwc_excelhelp]

I am somewhat new to lookup formulas. I have searched online and have been unable to find the right formula that incorporates all of the elements I need to make this work. I'm running out of time for this project, so I thought I would post here to see if anyone can help.

My data is set up like so (this is a sample, my full set includes all 12 months and about 436 rows of data):

Area	ID#	Name	January	February	March
7	0000000	514		Bacteria
7	0000000	514		Nitrate & Nitrite
5	0000000	Auto Dealer	Bacteria		Bacteria
5	0000000	Auto Dealer			Nitrate
2	0000000	Well		Bacteria

<tbody>
</tbody>

What I am looking to do is pull data in the cells to the right of the system name into another tab within this workbook. You can see some cells are blank and some systems have more than one row that contains a sample for the given month. I understand the limitations of vlookup and have experimented with others without any success.

I would like to ignore blank cells so no value will appear if the cell is blank, e.g. not put a zero as the output for a blank cell.

Any insight is much appreciated. Thank you.

<tbody>
</tbody>

 

[Aladin Akyurek]

Would you care to post the output you require for the sample above?

 

[gwc_excelhelp]

Would you care to post the output you require for the sample above?

I would like the samples required, i.e. "bacteria" or "nitrate" to appear in another tab in the workbook. If two samples are scheduled for the same month, I would like to be able to pull both into the other tab. Please let me know if you need more information.

 

[AliGW]

How do you want them to appear on the other tab? Please post a sample of the output requires, as requested above.

 

[gwc_excelhelp]

How do you want them to appear on the other tab? Please post a sample of the output requires, as requested above.

Hopefully, you can open this and see what I mean.

https://drive.google.com/a/gilfordwell.com/file/d/0B0iPcNeQ8IV3dFdxcGRmc0hIb2M/view?usp=sharing

On another note, I previously used =IFERROR(VLOOKUP($A$5,Sheet1!C2:O488,8,0)," ")
It worked fine, but only returns first value.

I hope this helps. My apologies if it doesn't, this has been exhausting!

 

[BarryL]

Maybe try for the google drive example

=INDEX(Sheet1!1:3,MATCH("514",Sheet1!C1:C3,0),MATCH(LEFT(C14,SEARCH(" ",C14)-1),Sheet1!1:1))

 

[BarryL]

Maybe try for the google drive example

=INDEX(Sheet1!1:3,MATCH("514",Sheet1!C1:C3,0),MATCH(LEFT(C14,SEARCH(" ",C14)-1),Sheet1!1:1))

assuming C14 contains February 2017