×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel Vlookup

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.

RE: Excel Vlookup

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

RE: Excel Vlookup

(OP)
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

RE: Excel Vlookup

For single sum use SUMIF, for multiple criteria SUMIFS.

combo

RE: Excel Vlookup

(OP)
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

RE: Excel Vlookup

(OP)
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.

RE: Excel Vlookup

(OP)
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

RE: Excel Vlookup

VLOOKUP cannot return multiple values.

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

RE: Excel Vlookup

@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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Vlookup

Quote:

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

That sounds like a simple PivotTable.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Vlookup

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.

RE: Excel Vlookup

???

Quote:

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.
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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close