Vlookup i think.............

Hi All

Newbie here looking for some help please.

I'm working on a spread sheet where I need the information from multiple columns in one spread sheet (Component Costs) and return them to a master worksheet (Master), however it only returns the first row.

The components worksheet as around 31K rows and 10 columns, however I only need the information from 4 only these columns.

Here is the formula I have used, =VLOOKUP(B4,'Component Cost'!A2:J31011,6,FALSE) for each column and I've attached images of worksheets to help.

Thanks
Aide

Hi.

I think you need to use a sum if function instead of vlookup. The latter only returns the first value.

Also, It looks like you forgot to "lock" the formula so you might get the wrong result for the next rows.

When you use A2:J31011 and copy down the formula, the next row will have formula A3:J31012

I suggest using A:J instead. The function will search all rows. I

Best regards Tbez

Thanks both for your replies and the warm welcome Flameretired

Here is a simple workbook, if you can guide me in the right direction it would be much appreciated.

Thanks
Aide

Hi,

Sorry, I am really a newbie and don't understand the concept of array enter formula, also when I enter the formula into D2 it didn't seem to do anything.

Aide

I offer an option to the formula without a massive input.

Formula:

Please Login or Register  to view this content.

Hi,

Formulas can be good for a situation like this, but sometimes they can be more trouble than they are worth.

A Pivot Table can be very effective for this type of lookup. Add in a Slicer and you have easier access to returning the data you want.

The attached is a modified version of your file with a Pivot Table and Slicer on your Master sheet.

Hope this is helpful.

Cheers

Originally Posted by Czeslaw

I offer an option to the formula without a massive input.

Formula:

Please Login or Register  to view this content.

Hi Czeslaw

Your formula worked thanks, however my original workbook has about 31000 rows in the component cost worksheet and I'm unable to emend your formula so it can return these. I think this is the part of the formula which requires changing 'Component Cost'!$A$2:$A$28=$B$4). Can you please advise how I can emend the formula?

Thanks
Aide

Increased range up to 40,000.

Formula:

Please Login or Register  to view this content.