Search code examples
excel-formulaexcel-2007

HLOOKUP() to look up ONE row only. No header

I have a look up table in another sheet, for a room booking system. The "ROOMS" table is simple an X-Y row with a header:

enter image description here

In the main data sheet, I want to look up the rooms, but highlight RED/YELLOW rooms that have already been "booked" on the same row, as another room reference.

Here we have a normal booking where both rooms are different. That's acceptable:

enter image description here

But here we have the same room booking on both and they are highlighted RED:

enter image description here

This works because I have a Conditional Format on every Room Allocation cell like this: 

=OR(I2=K2,I2=Q2,I2=T2,I2=W2,I2=Z22,I2=AF2,I2=AI2,I2=AP2,I2=AS2,I2=AV2,I2=AY2,I2=BB2,I2=BE2,I2=BK2,I2=BN2,I2=BQ2,I2=BT2) 
=OR(K2=I2,K2=Q2,K2=T2,K2=W2,K2=Z22,K2=AF2,K2=AK2,K2=AP2,K2=AS2,K2=AV2,K2=AY2,K2=BB2,K2=BE2,K2=BK2,K2=BN2,K2=BQ2,K2=BT2)

etc.

What I want to know, is there a way to do this using HLOOKUP on ONE row?

The reason I ask about the "one row" situation is that according to this, the table array has to be a genuine table with a header and at least one row of data.

But as you can see above, I only have ONE row to work with, which is data only.

Solution

  • It looks like you could use a COUNTIF on that:

    =COUNTIF($2:$2,A$2)>1

    Note the absolute references. That should work across all of row 2, but you can tweak it a bit if you want it to only apply to certain columns.