Searching values in rows

PT

Board Regular
Joined
Feb 4, 2005
Messages
103
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
Joined
Oct 17, 2012
Messages
381
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
Joined
Feb 4, 2005
Messages
103
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
Joined
Apr 18, 2011
Messages
37,894
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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:
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top