Look Up Formula With Veried Search Range

[Nella]

Hello, I am converting PDF files to excel via a copy paste method. After I get my data into excel, I can use a V-lookup to find my date and Associate name. My problem is, if the name is too long on the PDF it goes into 2 lines. This causes my additional billing information to no longer be in the main invoice row in excel. I want to find a code "L120" which should be in the 5th column (E) of Row 5 as shown below. However my Associate name is too long in row 6, so part of the name is now in row 7 of excel due to the length. How can I look up "L120" when it actually has been pushed to row 8 in excel and column 1 (A)?

The problem is as you can see, my invoice lines sometimes end up being 1 row in excel or 3+ rows in excel. I have found a formula to gather the billing code I need, but the search range is different for each line of the invoice, so how do you find a code for 10 lines of an invoice spanning 27 rows in excel and the column changing as well?

Below is a made up example of my situation for a visual representation.

Line 5 of my invoice is in row 5 of excel:

Line 6 of my invoice is in rows 6 - 8 of excel:

Thanks for any ideas you can pass my way.

 

[Becky]

Welcome to the forum

If the data has copied across in a disjointed way, your best bet might be to copy it from the pdf to a Word document first, and then into Excel. Guidance here: https://www.economicsnetwork.ac.uk/tips/pdf2excel

 

[Nella]

Thanks, but I tried this and the data still comes out in the same row format that I get. Is there a way to generate the data range using a formula?
example: formula I want to use is: =IFERROR(INDEX(A65:A75,(MATCH("L120",A65:A75,0))),INDEX(A65:E65,(MATCH("L120",A65:E65,0)))) The bold ranges need to be automatically updated for each line.
I have the formula to find my data range for each line, I just done know how to get the range into the formula.

I did think of a new way to approach this, but now my trouble is working vertically. How do I look up a value vertically an then use my match formula to find the L120 code? My formula is: =INDEX($A:$A,MATCH($M$2,$A:$A,0)), but if "L120" is in Row 7 and 15, the formula will stop at row 7 all the time. I need the Match formula to look at each invoice line section of my data.