Vlookup and merged cells

Did you read this first?
mergedcells.jpg

don't merge cells, set white color of fonts for unnecessary cells

Assuming your data starts in row 3, in G3 copied down, try this formula

=INDEX($C$3:$C$13,AGGREGATE(15,6,(ROW($C$3:$C$13)-ROW($C$3)+1)/($A$3:$A$13=INDEX($E$3:$E3,MATCH(9^99,$E$3:E3))),ROWS($G$3:G3)-MATCH(9^99, $E$3:E3)+1))

I could put up an example like you did but that's not very useful. Attached is the example

Honestly, Hkaric.... don't do it. Merged cells are a disaster in a sheet of data. You will find any sort of subsequent manipulation difficult or impossible. What you are starting with is a PERFECT data layout - one row one data unit. Don't f*** it up by using merged cells.

How about inserting 2 columns to give you the look you want (insert before column A) then hide current columns A and B

Building off of Sandy's suggestion above, you could use conditional formatting to color all of the unwanted fonts white at once. If your data is in A3:C16, select A3:B16 and add a new conditional formatting rule with the following formula:

=ROW($A3)-1<>ROUNDUP(COUNTIF($A$3:$A$16,$A3)/2,0)+MATCH($A3,$A$3:$A$16,0)

Select white font and it should white out the undesired entries. It's not perfectly centered for entries with an even number of rows, but it looks decent and leaves your data in place for future calculations and lookups.

To add a break between IDs, create a new CF rule for A3:C16 with the following formula:

=$A3<>$A2

...and format the rule to only add a top border. You can add vertical borders easily enough without CF.

Here's how it looks on ChemistB's sample:

I'm not sure I'm understanding your request correctly - you want to use conditional formatting to highlight the most recently entered row? Will that row always be at the bottom, or could it be anywhere? Do you want the highlight to go away once all 3 columns have been filled in that new row, or should it stay highlighted even after that?

To my knowledge, it's possible to use conditional formatting to highlight the bottom row. It's also possible to use conditional formatting to highlight a row in which data has been entered in one column but not (yet) in another column. If you want either of those two effects, we can do that with formatting. If you want the "newest" row to be highlighted regardless of where it is or whether or not the columns are full, we would need VBA for that.