Automated vlookup

[roshukha]

Hello All,

I am using vlookup function to replace some values from one cell to another. But, I have a huge range of data from which vlookup function should match the value.

In reference to the screenshot, How can I terminate the function when DMS_ORIG is not 'Birmingham AL CSA'?

Is it possible to use range $J$2:$N$4777 (end of sheet) and the function will terminate when DMS_ORIG becomes other names than 'Birmingham AL CSA'? And, I want to do the same thing for next section (the section below the box). How can I do that?

I am attaching my excel file with this thread.
Capture.jpgExcel_forum.xlsxCapture.jpg


Thanks.

[daffodil11]

Cool. First insert a new column so that you can keep the division consistent throughout the sheet, otherwise you will have to repoint the formula to each division's name.

In A3, I copied in Birmingham, A79 Mobile, etc. Then selected A3:Axx, hit F5, Special.., select blanks, hit =, hit Up arrow, and hold Ctrl and hit Enter. Instantly fills in all blanks with the headers.

Now, D3:

=IFERROR(LOOKUP(2,1/((ISNUMBER(SEARCH($A3,$I$3:$I$4777)))*($K$3:$K$4777=$B3)),M$3:M$4777),"0")

and copy over and down

[roshukha]

Thanks daffodil11.