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 --
-- 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)
Workbook(2)
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)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)
| B | C | J | N | P | |
| 1 | Location | Name | In Date | Empty Status | Out Date |
| 2 | G1 | DEF | 5/17 | y | 5/18 |
| 3 | Y1 | GHI | 5/17 | y | |
| 4 | G1 | JKL | 5/18 |
Workbook(2)
| B | C | D | E | F | |
| 1 | Location | Name | |||
| 2 | G1 | ### | |||
| 3 | G2 | ### | |||
| 4 | G3 | ### |
