Formula to search sheet for 2 indicators in same row and return specific info to cell

OK, so I am not sure if this is possible but if it is I know the people here can help me out as you always have.

I have attached a sample sheet for you to work with. This is tough to explain

What I am looking to do is have a formula that searches the whole DATA sheet columns E and O for specific criteria and if both criteria are met in the same row return the cell A data combined with the last 2 numbers of cell C in that row to a specific cell on the test sheet.

On the sheet I have attached I included and example results sheet also so you can see what it should look like. I have tried sample formulas with INDIRECT, INDEX, VLOOKUP, IF, AND etc and I just can't get it to produce a result. Not sure if I need VBA for this or if it can be done at all.

Your help is greatly appreciated!

I think the answer I just provided for someone else on Concatenate would probably work for you too:

http://www.excelforum.com/excel-form...dex-match.html

You could then do a simple If then statement. If the concatenated cell = 5/0/0/2 (or whatever you were looking for) then display X else, display a blank.

jlax34 thank you for the quick response. Unfortunately I don't think that is what i am looking for or maybe i am doing it wrong. I need a formula for a cell on the "TEST" sheet that will search the DATA sheet for 2 specific criteria and return that rows cell A and C information. This way only works if I have a separate look up table and even then I can't figure out how to return the column C last 2 numbers in the result with vlookup. I will keep messing around with your idea and see if I can get it to work. Much appreciated though!

I was thinking more along the lines of an IF(AND statement so that if both criteria are met then that specific rows A and C data are returned.

martindwilson thanks for the reply. I am trying it now...

that doesn't seem to be working either...it is returning a 0. I may be implementing it wrong. Can you try it on the attached sample sheet and see if it works for you?

I am not really sure why column B on the test sheet would make any difference in the formula though...it should be looking for all information in the "DATA" sheet

OK great...that does work. I used your sheet and found what I was doing wrong in mine. Thanks a lot!!

I do have one question that I didn't really think about until now. If you look at the last example in cell I7 it is listed as only Cambridge 10, but the data sheet has both Cambridge 10 and Cambridge 12 on that 5/0/2/2. Is there a way to get that result to read Cambridge 10, 12 in cell I7?

I don't want Cambridge repeated in that cell and per domain (IE 5/0/0/2, 5/0/2/2, etc) that first name will be the same only the 2 digits at the end will change. There could be many different names in column A though but only 1 per domain.

OK Thanks again for all your help! Much appreciated!

I will keep trying to see if I can get that formula where I need it