Dynamic VLOOKUP

I have an extract that contains one or more rows for a given ID and on a separate sheet I need to modify the VLOOKUP formulae to only pick up the first occurrence for each ID. On the next row I then need the first occurrence of the next ID and so on. I have played about with this formulae to ensure that subsequent VLOOKUPs start at the row below the last selected row, but this only picks up the next occurrence of the same ID.
=VLOOKUP(Sheet2!$D$2,INDIRECT("JLSExtract!A"&P2&":Q"&Q2),2,FALSE)

I use ROW() to return the last selected row in cell P2 and the bottom row in cell Q2

In effect I need to remove duplicates from the extract but the reason fro the VLOOKUP formula is that the selection is dependant upon data in the second column.

Hope someone can help

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Try this:

In R3 of JLSExtract sheet enter formula:

Please Login or Register  to view this content.

and copy down all the way. This identifies the first records for the corresponding ID from B1 of Sheet1.

In Sheet1,D1 enter:

Please Login or Register  to view this content.

this tells us how many records matched and is needed for the next formula.

Change column headers in row 2 of Summary to match exactly to corresponding column headers in the other sheet.

Then apply formula in A3 of Sheet1:

Please Login or Register  to view this content.

copy down as far as you want and across as far as needed.

I would recommend that if you can sort the records in the JLSExtract sheet, then it would make life a lot easier, if you want to see the results grouped by Service.

If you can do that, then assuming you list the services you want to find in say a vertical column A2:A31, then in JLSExtract, R3 use formula:

Please Login or Register  to view this content.

copied down.

and in Sheet1, D1 keep formula:

Please Login or Register  to view this content.

and in Sheet1, B3 where the extractions begin, use similar formula:

Please Login or Register  to view this content.

copied down and across.

Originally Posted by wawansur

Please Login or Register  to view this content.

What the meaning

Please Login or Register  to view this content.

it is referencing the entire column R.. so looking for the Maximum in the whole column.