Locate Cell and enter corresponding value into new cell

[noclue2000]

Hi All,

My request may be simple but I cannot work it out.

I'll start by explaining that Column B and C were copied and pasted from a different Excel sheet and are not part of the original document. My basic issue is that the original document does not contain the Invoice number relating to the ticketing number. Therefore, as per the attached image. I want to locate the value in Column B (Ticket Number) within Column A (Additional Information). You will notice Column A 'Ticket Number' listed but it has 081 as the first 3 digits followed by the rest of the number as seen in Column B. This means when searching, for the values from Column B in Column A, it is not an exact match but a simple Ctrl+F works fine. I then need the corresponding (Existing Invoice No) from Column C to be entered into Column D (Invoice Number) on the same row as the ticket number from Column A.

Hopefully this makes sense. Any help would be great!

 

[myall_blues]

How about this formula in column D. You will need to adjust the ranges to suit your situation.

=XLOOKUP(VALUE(TEXTBEFORE(TEXTAFTER(A2,"Ticket number: 081"),"Passenger")),$B$2:$B$5,$C$2:$C$5)

 

[noclue2000]

How about this formula in column D. You will need to adjust the ranges to suit your situation.

=XLOOKUP(VALUE(TEXTBEFORE(TEXTAFTER(A2,"Ticket number: 081"),"Passenger")),$B$2:$B$5,$C$2:$C$5)

Thank you Murray. I've tried that but it just produced an NA. Is that because in addition to trying to locate Column B(Ticket Number) t is also trying to locate Column C (Existing Invoice No) within Column A?

 

[myall_blues]

No it's not that.

Try this instead. I couldn't tell if your ticket numbers in column B were values or text from the picture. If that doesn't work I'll need a sample of your data. You can either share a link or preferably use XL2BB.

=XLOOKUP(TEXTBEFORE(TEXTAFTER(A2,"Ticket number: 081"),"Passenger"),$B$2:$B$5,$C$2:$C$5)