Thread: Vlookup/Index/Match help?

I need to find a simple formula that will find the status/locations listed in column C and copy them to the following rows in column A, but only if they have a valid ID and DCN listed. Someone previously helped me with a very nice formula that used a vlookup/Index/Match criteria but the problem is there were simply too many calculations, my excel file has 60000+ lines and when I try to use the formula it completely locks up.

I need something that will just copy the Status location down and will change when a new status location is listed.

Thank you in advance for any help you can provide.

What is a valid DSN number?

The best way to work with large amount of data and this kind of database is usually to use helper columns... These evaluate each row individually and do not create arrays of large values to manipulate.

In this case I added 2 helper columns.

I inserted 2 columns before column A

In A2, enter:

Code:

=IF(D2="Status/Location:",E2,A1)

and copy down.

This will extract the relevant Status/Location for each row...

In B2, enter:

Code:

=AND(LOOKUP(9.999999999E+307,--("0"&MID(D2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},D2&"0 123456789")),ROW($1:$30))))<>0,LOOKUP(9.999999999E+307,--("0"&MID(E2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},E2&"0 123456789")),ROW($1:$30))))<>0)

and copy down.

This looks for at least 1 digit in both columns B and C and returns TRUE if yes (that is my assumption).

In C2, Enter:

Code:

=IF(B2,A2,"")

This extracts the proper Status/Location if the result of column B is True
Th