Using a Lookup, then skip rows

**andersenterp** · 05-06-2009, 01:54 PM

I posted this earlier, but I guess I wasn't super clear. Basically, if I had a data table of two columns, one with names, the other with country, and I have a worksheet for each country. I just want to do an if statement and a nested lookup to bring over every name with that country. However, I want to do this so that the worksheets are smart enough to only list the names with the country, and skip to the next name with that country. That way, I could paste the raw data at any time, and the worksheets will update accodringly. Can anybody help with this?

**JBeaucaire** · 05-06-2009, 02:25 PM

I do this sort of thing frequently. I usually add a "KEY" column to my data to make transferring over to the other sheets a simple case in INDEX/MATCH with a ROW() formula. Should be very peppy.

Can you post up a sample workbook with 1 or 2 of the results sheets showing your desired results?

Click on GO ADVANCED and use the paperclip icon to attach a workbook.

**andersenterp** · 05-06-2009, 03:13 PM

For some reason, I can't reply to the thread I started originally. To the person who replied to the previous thread, i have attached a sample file. I basically need a formula on the country worksheets that will bring over the names when it is that particular country. The formula should be set so that, as this data changes, so will the corresponding worksheets and will bring over only the names with that country without skipping any rows. Also, it is alot more data than this, but I just did it small for simplicity sake.

**modytrane** · 05-06-2009, 03:38 PM

here's one way to do it.
see attached file.
modytrane

**andersenterp** · 05-06-2009, 03:44 PM

wow, can you explain that array formula to me?

**JBeaucaire** · 05-06-2009, 03:58 PM

My non-array approach which is possible with the addition of a filtering key to the info on the DATA sheet. Once that's there, an INDEX/MATCH can bring it over for each sheet. This will scale up to datasets with 10s of thousands of entries without the performance loss you get with array formulas.

Options...

This version also has a naming trick in the cell with Country. To create a new sheet, you need only duplicate an existing sheet and give it a unique name, the sheet will then automatically be working for that country.