Searching values in rows

[PT]

Hi to all in the forum,

I have the following data in column A:

A1- TP688360
A2 - I-TP1618
A3 - TP679934
A4 - I-TP88
A5 - TP680781
A6 - I-TP2518
A7 - TP688817
A8 - TP688818
A9 - I-TP1618
A10 - TP688340

As you can see I have data staring with TPXXXX and on the next cell I have something started with I-TP1618. The TPXXXX is a baggage number, the I-TP1618 is the corresponding flight. What I pretend is put in column B in front of TPXXXX the corresponding flight. How can I do that?

Thanks.
Ismael

 

[Sam_D_Ben]

PT,

Try this,

Excel 2007
	A	B
1	Baggage No.	Flight
2	TP688360	I-TP1618
3	I-TP1618
4	TP679934	I-TP88
5	I-TP88
6	TP680781	I-TP2518
7	I-TP2518
8	TP688817	TP688818
9	TP688818	I-TP1618
10	I-TP1618
11	TP688340
Sheet1

Cell Formulas
Range	Formula
B2	=IF(LEFT(A2,1)="T",OFFSET(A2,1,0),"")

Muz.

 

[PT]

Hi Muz,

It works when i have just 1 baggage between flights. When we have more than one, doesn't work. See your cell A8 it should return I-TP1618.

Thanks.
Ismael

 

[Rick Rothstein]

It works when i have just 1 baggage between flights. When we have more than one, doesn't work. See your cell A8 it should return I-TP1618.

Does this formula do what you want?

=IF(A2="","",IF(LEFT(A1)="T",VLOOKUP("I-*",A2:A$100,1,0),""))

The red highlighted text is an absolute reference to a cell that is at or below the last data item in Column A.

 

[PT]

Hi Rick,

Yes it does. Thanks.

Regards.
Ismael