Look up matching value based on certain conditions?

WSBirch

Board Regular
Joined
Apr 10, 2018
Messages
59
Office Version
  1. 365
Platform
  1. Windows
Hi there,
I'm looking to find specific values using two different workbooks. There will be instances where the same value would be returned, but I only want that value returned if it matches a couple of conditions.

On workbook(2), I want C2 to return any matching IDs from workbook(1). Normally, I'd write a vlookup like --
Excel Formula:
=VLOOKUP(B2,'[WORKBOOK(1).XLSM]'!$B:$C,2,FALSE)
-- however, that'd return "DEF" from C2 from workbook(1). What I want is C4 in this instance. I want to find a matching value to B2 on workbook(2), from workbook(1) where it matches B2 but also where there's no value in column P. So in this case, it would see B2, then look at P2 to see if there's a value (yes), so then it ignores B2 and keeps looking for another matching value in column B that also has no value in the corresponding row of the P column (which is B4, to return "JKL").

On Workbook(2) I would need the formula copied down C2:C69

The general function:
On workbook(1), it's basically a running list of data. We're assigning various objects to certain locations, but there can only be 1 object at any given location. An object full of things is assigned to an available open location, is emptied, then removed from the location. Once an object is removed from the location (has an "Out Date") then the location is available and open for a new, full, object. We need to maintain the list of past locations in this list of data down column B, so there's going to be a great number of times where the value we're looking for in B2 will match a lot of different objects, but we only want the one that doesn't have an "Out Date" because it's the only one actually at the assigned location. We need to use workbook(2) to track what object is currently at a location. There's a chance I'll need to use this function to track similar things.

Workbook(1)
BCJNP
1LocationNameIn DateEmpty StatusOut Date
2G1DEF5/17y5/18
3Y1GHI5/17y
4G1JKL5/18

Workbook(2)
BCDEF
1LocationName
2G1###
3G2###
4G3###
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
I should preface that I do, eventually, want the formula written in VBA at some point. For now, I'd need to understand the basics here on if this is functional.
 
Upvote 0
I have found a solution to this issue, and marking my own post as solved.
 
Upvote 0
I have found a solution to this issue, and marking my own post as solved.

Good to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.
 
Upvote 0
Good to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.
I'd love to. After enough googling, I did find it on a different website, am I ok to link to the website? It solves my exact question. I just want to make sure I'm okay to post the link. If so, I'll edit my solved post right away with it. I can write it all out, for the array formula I'm using, but it's kind of a lot of explain from the webpage it's at and since I was the one looking for help I don't know if I'd do it justice for explaining how/why everything in the formula is doing it.
 
Upvote 0
I'd love to. After enough googling, I did find it on a different website, am I ok to link to the website? It solves my exact question. I just want to make sure I'm okay to post the link. If so, I'll edit my solved post right away with it. I can write it all out, for the array formula I'm using, but it's kind of a lot of explain from the webpage it's at and since I was the one looking for help I don't know if I'd do it justice for explaining how/why everything in the formula is doing it.
It is fine to post the link that answered your question, then you can mark that post as the solution. This way, someone else who might need the exact solution and found your question can also find it useful.
 
Upvote 0
It is fine to post the link that answered your question, then you can mark that post as the solution. This way, someone else who might need the exact solution and found your question can also find it useful.
Thank you!

I did find a solution to my exact problem, using conditional statements inside of a VLOOKUP formula to find a matching value based on two criteria, here:
How to use VLOOKUP with multiple conditions
 
Upvote 0
Solution

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top