Forum

Notifications
Clear all

Help with a VLOOKUP - INDEX-MATCH approach

6 Posts
2 Users
0 Reactions
63 Views
(@gabster77)
Posts: 3
Active Member
Topic starter
 

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

 
Posted : 25/04/2019 10:18 am
Anders Sehlstedt
(@sehlsan)
Posts: 972
Prominent Member
 

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.

 
Posted : 25/04/2019 4:32 pm
(@gabster77)
Posts: 3
Active Member
Topic starter
 

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

 
Posted : 26/04/2019 3:27 pm
Anders Sehlstedt
(@sehlsan)
Posts: 972
Prominent Member
 

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.

 
Posted : 27/04/2019 1:55 am
(@gabster77)
Posts: 3
Active Member
Topic starter
 

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

 
Posted : 27/04/2019 8:56 am
Anders Sehlstedt
(@sehlsan)
Posts: 972
Prominent Member
 

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.myonlinetraininghub.com/excel-factor-entry-4-index-and-match-two-criteria

 
Posted : 27/04/2019 6:27 pm
Share: