Return cell value if conditions met, without duplications

[tlc53]

Hi,

I'm not sure how to use Match with an IF statement, or if it's even possible to do. Here is what I am hoping to achieve;

Car Type
B14:B23

Tax Treatment
E14:E23

Private %
F14:F23

Input Data
B28:B37

I would like the Input Data B28:B37 to be pre-populated depending on what the Tax Treatment and Private % are.

So if Tax Treatment = "Private Portion" AND Private % <.75 then populate Input Data with Car Type.

Eg. E14 = "Private Portion AND F14 <.75 then B28 = B14.

So all entries meeting this criteria will be shown in cells B28:B37 but with no duplications.

Hope I've explained that clearly enough! Thank you

 

[Trevor_S]

Don't see that you need MATCH, all ranges are ten rows, with each row of Input Data looking at a single row of other data. So in B28:

=IF(AND(E14="Private Portion",F14<0.75),B14,"")

Then copy down to the other rows in the Input Data range.

 

[tlc53]

Thank you, I did think of doing it that way. However, I don't want any empty rows.
So for example, if Row 1 and 4 meet the criteria, I want this to show in Rows 1 and 2 below, so there are no empty rows (ie. it's all descending).
Hope that makes sense.

 

[Trevor_S]

That's trickier. You could do it with a helper column to the left of Car Type (i.e. column A). You could put a formula in there that looked for a match on that row - if there was a match, add one to the value above, otherwise repeat the value above. This would then give a 1 on the first match, 2 on the second, and so on, which you could find with a VLOOKUP:

A14 formula (copy down to A23):
=IF(AND(E14="Private Portion",F14<0.75),1,0)+A13

B28 formula (copy down to B37):
=IFERROR(VLOOKUP(ROW(B28)-27,$A$14:$B$23,2,FALSE),"")

By deducting 27 from the row number, row 28 will find the first match (when column A becomes 1), row 29 will find the second match (when column A becomes 2) and so on. Assuming not all ten match, the VLOOKUP for the later rows will result in an error, which the IFERROR part if the formula will turn into a blank.

 

[tlc53]

Thanks Trevor.
There's more than one way to skin a cat and your formula (especially the second part) really helped me.
The problem with the first part was that it would add 0 if the one before did not meet the criteria, making the result 1 even if it was previously used. If I looked at summing all results above that would result in double counting (example below, no.5 would return 7, instead of 4).

1. Criteria met YES = 1
2. Criteria met YES = 2
3. Criteria met YES = 3
4. Criteria met NO = 0
5. Criteria met YES = 1

With a bit of working on the side, I was able to RANK them and then use your VLOOKUP formula. It now works exactly how I was hoping it to.
Thanks very much for all your help!!