Log In
Register
Home
Active Member
April 16, 2019
Offline
Hello,
This is my first post, so let me know if I did not follow a forum rule properly.
I'm trying to figure out how to build a formula to do the following. I'm getting a bit lost with the approach, should it be VLOOKUPs with IFS, or a simple IF or an INDEX/MATCH with something else or something entirely different.
I need to look in a list where a product can be found under one or more codes. I need to know if the product I'm looking for is found under a specific code in the list. If not I need an error message saying "Not found". However if it's found I need a simple "OK" or maybe even bring back the code itself message.
Example: Is "ProLiant DL380 G5" found in the list under C1 code?
My list is a master list where it was decided which product should be associated to which code and it can be a normal behavior that a specific product is under two different codes.
Thank you in advance
See Answer
VIP
Trusted Members
December 7, 2016
Offline
Hello,
If it is just a simple check like you describe then it is easiest to use DGET function. See attached file for an example.
Active Member
April 16, 2019
Offline
Hi Anders,
Thank you for replying on this issue, your time is appreciated. Now that I understand how DGET works. I'm struggling to get this copied to a list where we have multiple Products entries each with a code. How would I go about applying this formula to each row?
My challenge is that I have over 80k lines that I have to apply this formula.
=IF(ISERR(DGET(A3:B20,"Code",I7:J9)),"Not found","OK")
I've uploaded your file with a clearer reality of what I'm facing (added lines in orange).
Regards
VIP
Trusted Members
December 7, 2016
Offline
Hello Gabriel,
There is no file.
It would be good if you can upload a sample file showing how you want the results to be presented.
Active Member
April 16, 2019
Offline
yeah sorry about the missing file, here it is. I also added 2 more worksheets which are showing the actual reality of how the data is formatted and setup. I hope it will be clear enough.
Regards
VIP
Trusted Members
December 7, 2016
Offline
Hello,
I am sorry, I don't understand what you are trying to get from that data.
Anyway, DGET function is just a lookup function for one or several rows, each row is equal to an AND function. If you want a validation check for each and every row in your list, then either use INDEX & MATCH or VLOOKUP with some helper columns.
Check this article to get some ideas of how to build your formula.
https://www.myonlinetraininghu.....o-criteria
Answers Post
1 Guest(s)
