Return column header where cell value = "X"

[davehat]

I have two sheets, sheet1 and sheet2, each containing information about tickets. Both sheets contain a column containing ticket numbers.

The data in sheet2 is a subset of that in sheet1. It contains a series of columns that define the status of a ticket, such as used, refunded, exchanged.

The tickets are listed in column B and the status is defined in the column header (C1:M1) like so:

-------------------------------------------------
|Ca |Frm/SerNo |VK|VX|EX|ER|RF|CR|TR|CX|NR|RR|AC|
-------------------------------------------------
|000|nnnnnnnnn1|X |  |  |X |  |  |  |  |  |  |  |
|000|nnnnnnnnn2|X |  |  |X |  |  |  |  |  |  |  |
|000|nnnnnnnnn3|X |  |  |  |X |  |  |  |  |  |  |
|000|nnnnnnnnn4|X |  |  |  |  |  |  |  |  |  |X |
|000|nnnnnnnnn5|X |  |  |  |  |  |  |  |  |  |  |
|000|nnnnnnnnn6|X |  |  |  |  |  |  |  |  |  |X |
-------------------------------------------------

In sheet1, I have a larger group of tickets with a variety of information. I need to add a column to this sheet that will return the status of the ticket as defined in sheet2.

Additionally, if a ticket on sheet1 is not present in sheet2, I need to return the status "UN". Also, if a ticket is present on sheet2, but there is no "X" in any column, I needs to return the value "OP".

Presently, I am doing this across two rather messy formulae:

1. Write the status to a cell in a new column in sheet 2 (N)

{=IF(ISNA(INDEX('sheet2'!$D$1:$M$1,MATCH(TRUE,'sheet2!D2:M2="X ",0))),"OP",INDEX('sheet2'!$D$1:$M$1,MATCH(TRUE,'sheet2'!D2:M2="X ",0)))}

2. Do a vlookup on to this value from sheet 1

=IF(ISNA(VLOOKUP(K2,'sheet2'!$B$2:$N$145,13,FALSE)),"UN",(VLOOKUP(K2,'sheet2'!$B$2:$N$145,13,FALSE)))

I am totally stumped as to how to do this in a single formula (or even if it is worth putting it in one)...

I have no idea whether the next two questions have mutually exclusive answers but it boils down to this:

A) Can any one suggest a more elegant solution?

B) Does any one know if it would be possible to combine these formulae?

 

[sous2817]

Hello,

Does this get you close?

=LOOKUP(2,1/($A2:$K2<>""),INDEX($A$1:$K$1,1,COLUMN($A2:$K2)))



It should return the value in the first row for the last column used. Adjust ranges to suit.

 

[davehat]

Hello,

Does this get you close?

=LOOKUP(2,1/($A2:$K2<>""),INDEX($A$1:$K$1,1,COLUMN($A2:$K2)))



It should return the value in the first row for the last column used. Adjust ranges to suit.

It might, but I am a bit confused about what ranges to add where as my formula needs to reference two sheets...

For arguements sake, let say that the ticket number is in column A in sheet1. I need to return the status in sheet2 into column B of sheet1.

Simplifying my current formula (in column N of sheet 2) gives:

INDEX('sheet2'!$D$1:$M$1,MATCH(TRUE,'sheet2'!D2:M2="X ",0))

The ISNA wrap on the formula in my original post is there to output "OP"

then the formula in column B of sheet 1 would be:

VLOOKUP(A2,'sheet2'!$B$2:$N$145,13,FALSE)

The ISNA wrap on this formula in my original post is there to output "UN"

(I should also point out that I am only looking at the columns from D onwards in sheet 2 as column C always has an "X")

 

[sous2817]

OK, take 2:

=IF(AND(ISNA(LOOKUP(2,1/(Sheet2!$C6:$M6="X"),INDEX(Sheet2!$C$1:$M$1,1,COLUMN(Sheet2!$A$1:$M$1)))),ISNA(MATCH(A6,Sheet2!$B$2:$B$7,0))),"OP",IF(ISNA(MATCH(A6,Sheet2!$B$2:$B$7,0)),"UN",LOOKUP(2,1/(Sheet2!$C6:$M6="X"),INDEX(Sheet2!$C$1:$M$1,1,COLUMN(Sheet2!$A$1:$M$1)))))

If there isn't a "X" value in sheet2 and there isn't a ticket#, the cell returns "OP". If there is a "X" value, but no ticket#, the formula returns "UN". If there is a ticket # and there an an "X" value, the formula returns the header of the "X" value.

I think that's right. What do you think? This should replace both formulas. So, it would ultimately go where your VLOOKUP formula is now.

You'll need to extend / modify the Sheet2!$B$2:$B$7 bit in the MATCH formulas to match your sheet2 range. Otherwise, you should be able to put the formula in the 1st ticket row on Sheet1 and copy down.