Excel Vlookup

• Sep 4, 2020

• #2

combo

Technical User

Jan 1, 2003

4,191

PL

I don't know the data structure you have, but for me VLOOKUP is not a good tool for this.
In excel 2019/365 you may consider FILTER function.
In excel 2016+ Power Query may be used to join tables, input parameters and output to table or pivot table (requires refreshing after changing input).

combo

 

Upvote 0 Downvote

• Sep 4, 2020

• Thread starter
• #3

Cpreston

MIS

Mar 4, 2015

987

GB

You may be right, one thing that would work is if I could sum the vlookup, any ideas how I change the formulae to do this? I have googled a bit but cant find something suitable.
n
=IFERROR(VLOOKUP(A:A,'Paint Data'!A:E,3,FALSE),"")

Thanks

 

Upvote 0 Downvote

• Sep 4, 2020

• #4

combo

Technical User

Jan 1, 2003

4,191

PL

For single sum use SUMIF, for multiple criteria SUMIFS.

combo

 

Upvote 0 Downvote

• Sep 4, 2020

• Thread starter
• #5

Cpreston

MIS

Mar 4, 2015

987

GB

Tried both but my syntax just is not accepted

=SUMIF(VLOOKUP([WorksOrderNumber],'Paint Data'!A:C,3,FALSE),"") , I have tried to change it round but no luck

=SUMIFS(VLOOKUP([WorksOrderNumber],'Paint Data'!A:C,3,FALSE),"") it says I have entered to few arguments of it.

Any ideas on the syntax please

 

Upvote 0 Downvote

• Sep 4, 2020

• Thread starter
• #6

Cpreston

MIS

Mar 4, 2015

987

GB

Go this far

=SUMIFS(Table3[[#Headers],[WorksOrderNumber]],VLOOKUP('Paint Data'!A:A,3,FALSE),'Paint Data'!C:C)

But still not working, any ideas anyone.

 

Upvote 0 Downvote

• Sep 4, 2020

• Thread starter
• #7

Cpreston

MIS

Mar 4, 2015

987

GB

used this in the end =SUMIF('Paint Data'!A:A,[@WorksOrderNumber],'Paint Data'!C:C)

However, this just gives me the Total Process Cost , I need to show them individually, I will look into your power quesry method. Thanks

 

Upvote 0 Downvote

• Sep 4, 2020

• #8

strongm

MIS

May 24, 2001

20,237

GB

VLOOKUP cannot return multiple values.

But this looks to me like it could be solved with a pivot table

 

Upvote 0 Downvote

• Sep 4, 2020

• #9

S

SkipVought

Programmer

Dec 4, 2001

47,492

US

@cpreston, forget about VLOOKUP!!! You seem to be fixated on that function.

From what you have indicated a simple SUMIF is all you need, if indeed all that is required is one WorkOrderNumber to sum the TotalCostPrice.

Personally, I'd rather use SUMPRODUCT in place of SUMIF or SUMIFS or COUNTIF or COUNTIFS. But that's just me.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

Upvote 0 Downvote

• Sep 4, 2020

• #10

S

SkipVought

Programmer

Dec 4, 2001

47,492

US

However, this just gives me the Total Process Cost , I need to show them individually,

Click to expand...

That sounds like a simple PivotTable.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

Upvote 0 Downvote

• Sep 4, 2020

• #11

M

mintjulep

Technical User

Aug 20, 2004

1,552

JP

Agree with pivot table.

The new XLOOKUP() might be able to return multiple results. I haven't fully come to grips with its use yet.

 

Upvote 0 Downvote

• Sep 4, 2020

• #12

combo

Technical User

Jan 1, 2003

4,191

PL

A tip with new dynamic arrays, if you have 365:

https://theexcelclub.com/excel-dynamic-arrays-a-new-way-to-model-your-excel-spreadsheets/

combo

 

Upvote 0 Downvote

• Sep 4, 2020

• #13

S

SkipVought

Programmer

Dec 4, 2001

47,492

US

???

I also have another sheet with some information and I want to populate the costs into this sheet. They have works order number as the key yo match with.

Click to expand...

Well if you have "another sheet" that already has "works order number as the key yo match with" then the SUMIF formula will do the job.

???
You are giving mixed signals.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

Upvote 0 Downvote