Excel Vlookup
Excel Vlookup
(OP)
Hi
I have a sheet with information about costs
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.
In sheet 1 the works order number may have 3 different costs , or more or less, depending on what processes it goes through. Example is in the screen shot below.
I have used this vlookup to get the data =IFERROR(VLOOKUP(A:A,'Paint Data'!A:E,3,FALSE),"")
This only brings back the first result of 237.791, how can I get it so it shows the other 2, if that is at all possible, Thanks in advance.
I have a sheet with information about costs
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.
In sheet 1 the works order number may have 3 different costs , or more or less, depending on what processes it goes through. Example is in the screen shot below.
I have used this vlookup to get the data =IFERROR(VLOOKUP(A:A,'Paint Data'!A:E,3,FALSE),"")
This only brings back the first result of 237.791, how can I get it so it shows the other 2, if that is at all possible, Thanks in advance.
Talk To Other Members
RE: Excel Vlookup
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
RE: Excel Vlookup
n
=IFERROR(VLOOKUP(A:A,'Paint Data'!A:E,3,FALSE),"")
Thanks
RE: Excel Vlookup
combo
RE: Excel Vlookup
=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
RE: Excel Vlookup
=SUMIFS(Table3[[#Headers],[WorksOrderNumber]],VLOOKUP('Paint Data'!A:A,3,FALSE),'Paint Data'!C:C)
But still not working, any ideas anyone.
RE: Excel Vlookup
However, this just gives me the Total Process Cost , I need to show them individually, I will look into your power quesry method. Thanks
RE: Excel Vlookup
But this looks to me like it could be solved with a pivot table
RE: Excel Vlookup
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
RE: Excel Vlookup
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
RE: Excel Vlookup
The new XLOOKUP() might be able to return multiple results. I haven't fully come to grips with its use yet.
RE: Excel Vlookup
combo
RE: Excel Vlookup
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