# Searching values in rows

#### PT

##### Board Regular
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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Sam_D_Ben

##### Active Member
PT,

Try this,

Excel 2007
AB
1Baggage No.Flight
2TP688360I-TP1618
3I-TP1618
4TP679934I-TP88
5I-TP88
6TP680781I-TP2518
7I-TP2518
8TP688817TP688818
9TP688818I-TP1618
10I-TP1618
11TP688340
Sheet1
Cell Formulas
RangeFormula
B2=IF(LEFT(A2,1)="T",OFFSET(A2,1,0),"")

Muz.

#### PT

##### Board Regular
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

##### MrExcel MVP
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.

Last edited:

#### PT

##### Board Regular
Hi Rick,

Yes it does. Thanks.

Regards.
Ismael

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,673
Messages
5,838,706
Members
430,564
Latest member
Raeyven

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back