Lookup column A, return column C value where non-blank

[V8Hilux]

So this is only some test data to prove the concept.



Essentially I have a data set that will contain duplicated values in one column which I need to do a look up on with either blank cells or a unique identifier in another column. If the desire look up value is present in the first column, I want to return the adjacent non-blank value from another column. i.e. ignore the rows where the value is present but no unique identifier exists.



Here is the test data set and lookup table:

[TABLE="width: 512"]
<tbody>[TR]
[TD="class: xl81, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl81, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl81, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl81, width: 64, bgcolor: transparent"]C
[/TD]
[TD="class: xl81, width: 64, bgcolor: transparent"]D
[/TD]
[TD="class: xl81, width: 64, bgcolor: transparent"]E
[/TD]
[TD="class: xl81, width: 64, bgcolor: transparent"]F
[/TD]
[TD="class: xl81, width: 64, bgcolor: transparent"]G
[/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]1
[/TD]
[TD="class: xl66, bgcolor: transparent"]Animal
[/TD]
[TD="class: xl67, bgcolor: transparent"]Sold
[/TD]
[TD="class: xl68, bgcolor: transparent"]ID Tag
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Animal
[/TD]
[TD="class: xl67, bgcolor: transparent"]Treated
[/TD]
[TD="class: xl68, bgcolor: transparent"]Kept
[/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]2
[/TD]
[TD="class: xl69, bgcolor: transparent"]Cat
[/TD]
[TD="class: xl70, bgcolor: transparent"]ü
[/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]Alligator
[/TD]
[TD="class: xl70, bgcolor: transparent"]ü
[/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]3
[/TD]
[TD="class: xl72, bgcolor: transparent"]Frog
[/TD]
[TD="class: xl73, bgcolor: transparent"]ü
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"]Bat
[/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]4
[/TD]
[TD="class: xl72, bgcolor: transparent"]Dog
[/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"]01
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"]Cat
[/TD]
[TD="class: xl73, bgcolor: transparent"]ü
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]5
[/TD]
[TD="class: xl72, bgcolor: transparent"]Eagle
[/TD]
[TD="class: xl73, bgcolor: transparent"]ü
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"]Dog
[/TD]
[TD="class: xl73, bgcolor: transparent"]ü
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]6
[/TD]
[TD="class: xl72, bgcolor: transparent"]Eagle
[/TD]
[TD="class: xl73, bgcolor: transparent"]ü
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"]Eagle
[/TD]
[TD="class: xl73, bgcolor: transparent"]ü
[/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]7
[/TD]
[TD="class: xl72, bgcolor: transparent"]Alligator
[/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"]02
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"]Frog
[/TD]
[TD="class: xl76, bgcolor: transparent"]ü
[/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]8
[/TD]
[TD="class: xl72, bgcolor: transparent"]Frog
[/TD]
[TD="class: xl73, bgcolor: transparent"]ü
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]9
[/TD]
[TD="class: xl72, bgcolor: transparent"]Dog
[/TD]
[TD="class: xl73, bgcolor: transparent"]ü
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]10
[/TD]
[TD="class: xl75, bgcolor: transparent"]Cat
[/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"]03
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


There is a formula in Column F of =IF(ISNA(VLOOKUP(U3,$A:$A,1,FALSE))=TRUE,"","[?]") (where [?] is a windings tick)

So, going from the top, I want to look up E2 (alligator) in column A (animal). If it is there, I want to check column C (id tag) for a corresponding value, and if one is found return it or a windings tick.

In the example of "cat" it should ignore the values in row 2, as C2 is blank and instead return the results for row 10 where C10 is "03".

Some footnotes:

• "Treated" returns a tick if I have ever treated that type of animal - formula used listed above
• "kept" to return a tick only if I have kept/given an ID Tag to the animal type
• There will only ever be 1 kept animal of each type. i.e. if I chose to add another eagle and keep that one the current tagged one will be sold

I hope that makes sense? I have tried various things based off of searches on here and other sites, I've tried including INDEX, MATCH, ISNUMBER, ISNA etc with a standard Vlookup but I cannot seam to get it to ignore the blanks and focus on data only. Any help would be greatly appreciated.

 

[V8Hilux]

Not sure what happened when it got posted but those values in B & F are supposed to be Wingdings ticks

 

[V8Hilux]

No one have a solution?