VLOOKUP or SUMIF or ???

[dawgfan]

I have a workbook containing 2 tabs.

Tab 1 has columnar data with column A containing locations; however, each location may be listed multiple times on different lines - once for each piece of equipment at that location. Tab 1, column B has a piece of equipment listed. Tab 1, column C has a price associated with the piece of equipment in column B.

Tab 2 contains contains columnar data with column A containing locations. I need to populate Tab 2, column B with the sum of all equipment associated with the location in column A.

I have used VLOOKUP to try and find matches for the lookup_value in Tab 2, column A, then return the value of the price from Tab 1, column C.

Problems have been that it returns only the first instance of the location data, not a sum of all. I know the formula is missing something, but cannot ID it.

Formula as used:

=VLOOKUP(B4,'Tab 1'!$I$4:$AI$461,3)

B4 is the column on Tab B that contains all of the locations (lookup_table)
Tab 1 I4 thru AI461 is the table_array where all locations are listed
3 is the col_index_num indicating that the price is in the 3rd column over

If this is not enough data to ealuate, please advise.

I'm stuck and in need of immediate assistance to meet a deadline. Any assitance is much appreciated, thanks!

[NBVC]

Try perhaps,

=Sumif('Tab 1'!$I$4:$I$461,B4,'Tab 1'!$K$4:$K$461)

[dawgfan]

Tried that last night. It only returns zeros. VLOOKUP has done the best; however, it only grabs the first occurance.

[Shijesh Kumar]

can u post a sample spreadsheet (without sensitive data)

[NBVC]

Your column K is probably text instead of numbers...

try selecting column K and going to Data|Text to Columns and clicking Finish.

Does that fix it?

[dawgfan]

I've also used:

=SUMIF('Tab 1'!$I4:$I461,B4,'Tab 2'!$AI$4:$AI461)

It works equally as well as the VLOOKUP. They both grab the first occurance of a location only. I need something to get them all.

I noticed that the SUMIF also grabs the wrong amount once in a column of a dozen lines. Strange, as it got all the others right.

[dawgfan]

In my samples:

VLOOKUP: B = text, I = text, AI = number

SUMIF: I = text, B = text, AI = number

[dawgfan]

Your column K is probably text instead of numbers...

try selecting column K and going to Data|Text to Columns and clicking Finish.

Does that fix it?

I don't understand 'going to Data|Text'...???

[dawgfan]

can u post a sample spreadsheet (without sensitive data)

Trying to figure a way to do this.

[NBVC]

I don't understand 'going to Data|Text'...???

Select the column of "numbers" and go to the Data Menu at the top and select Text To Columns. or ALT+D+E, then click Finish.

[Shijesh Kumar]

Scroll down... below submit reply button

u will see manage attachemnt button click on that and upload a file

[dawgfan]

Select the column of "numbers" and go to the Data Menu at the top and select Text To Columns. or ALT+D+E, then click Finish.

Don't see the value of converting numbers in a text to column wizard? I'm obviously missing something.

[dawgfan]

Scroll down... below submit reply button

u will see manage attachemnt button click on that and upload a file

Yes, thanks. I meant how to use my sensitive data...

[NBVC]

Don't see the value of converting numbers in a text to column wizard? I'm obviously missing something.

Well, if you know you have exact matches in column I and they are just not adding up the numbers in AI, then chances are the numbers in AI are really formatted as text... the Text-to-columns fixes that... did you even try?

[dawgfan]

Well, if you know you have exact matches in column I and they are just not adding up the numbers in AI, then chances are the numbers in AI are really formatted as text... the Text-to-columns fixes that... did you even try?

now I see. No, did not try as this is a proposal and I cannot afford to mess anything up experimenting. I'll make a copy any play. It has benn suggested by colleagues that a formatting thing may be going on in the tables of data, which are huge and may have been derived from various sources including Word to Excel historically. You may be on the right track. Thanks again.

[dawgfan]

I gave up and added a subtotal to the table I was pulling cost from and used the SUMIF formula rather than the VLOOKUP.