Help: Formula - Populate Field /w Conditions

[happydays886]

Hello,

I'm looking to populate a field (yellow in file). I need help to determine how to do this. I think vlookup with an if statement is one approach, but I'm not quite sure.

It's difficult to explain what I'm trying to do, so please review the attached file.

My attempt to explain:

Vlookup data for a "certain type/condition" and pull back the date. In this case, I want to find the date for each number when the type is not equal to A or Z. All dates are the same for Types that are not equal to A or Z; dates are different between types A and Z.
Number|| TYPE || DATE
1...............A.....Jan1, 2016
1...............B.....Jan3, 2016
1...............C.....Jan3, 2016
1...............Z.....Jan2, 2016

In this case, I would want to pull "Jan 3, 2016" and ignore "1" where Type = A and/or Z.

- The problem is you're setting a restriction on the Vlookup to 'not' pull a date for the first instance of the number (i.e. 1, A, Jan 1, 2016); you're telling excel to ignore all instances of "a", and find the next instance of 1, where the type is <> A or Z. I don't know how to do that; or maybe vlookup is not the right function to use?

Help is appreciated!

[happydays886]

Thanks again in advance.

[Richard Buttrey]

Hi,

One way as an array formula in G5 copied down

Please Login or Register  to view this content.

[AlKey]

Here is the other way
A formula similar to Richards as a regular formula

Please Login or Register  to view this content.

[happydays886]

Yeah, that works.

I went with "=INDEX($B$2:$E$23,MATCH(G5&"0",$B$2:$B$23&$D$2:$D$23,0),4)"

As this formula bogs excel down; I made an if function to list A/Z = 1, <> = 0 in a new row "d".

Thank you everyone!