I am trying to incorporate the following logic within my query, which is kind of like 2 vlookups, if one isnt available, use the next.
=IFERROR(VLOOKUP(D2,'Segmentation'!$A:$C,3,0),IFERROR(VLOOKUP(C2,'Segmentation'!$A:$C,3,0),""))
So it should lookup into column D First (unit2) then if nothing lookup column C(value)
This is what I have so far, I have tried a OR join:
SELECT DISTINCT A.[unit], B.[Hierarchy] FROM [Mapping$] as A
inner join [Segmentation$] as B
ON (A.[unit2] = B.[Number] OR
A.[Value] = B.[Number])
WHERE B.[Hierarchy] <> ''
Order By A.[unit] asc
Also tried:
SELECT DISTINCT A.[unit], B.[Hierarchy] FROM [Mapping$] as A
inner join [Segmentation$] as B
ON Nz(A.[unit2], A.[Value])=B.[Number]
WHERE B.[Hierarchy] <> ''
Order By A.[unit] asc
Sample data in segmentation sheet:
+--------------+-------------+-----------+
| Number | Name | Hierarchy |
+--------------+-------------+-----------+
| 11773 | Stack | Overflow |
+--------------+-------------+-----------+
mapping sheet:
+-------+-----------+-------+-------+-----------+---------+
| unit | variable1 | Value | unit2 | variable2 | value2 |
+-------+-----------+-------+-------+-----------+---------+
| 11773 | lvl0 | 80000 | 11773 | lvl0_nm | thing 1 |
| 11773 | lvl1 | 89000 | 11773 | lvl1_nm | thing 2 |
| 11773 | lvl2 | 89022 | 11773 | lvl2_nm | thing 3 |
| 11773 | lvl3 | 89756 | 11773 | lvl3_nm | thing 4 |
| 11773 | lvl4 | 89833 | 11773 | lvl4_nm | thing 5 |
| 11773 | lvl5 | 11773 | 11773 | lvl5_nm | |
| 11773 | lvl6 | | 11773 | lvl6_nm | |
| 11773 | lvl7 | | 11773 | lvl7_nm | |
| 11773 | lvl8 | | 11773 | lvl8_nm | |
| 11773 | lvl9 | | 11773 | lvl9_nm | |
| 11773 | lvl10 | | 11773 | lvl10_nm | |
| 11773 | lvl11 | | 11773 | lvl11_nm | |
| 11773 | lvl12 | | 11773 | lvl12_nm | |
| 11773 | lvl13 | | 11773 | lvl13_nm | |
| 11773 | lvl14 | | 11773 | lvl14_nm | |
| 11773 | lvl15 | | 11773 | lvl15_nm | |
| 11773 | lvl16 | | 11773 | lvl16_nm | |
| 11773 | lvl17 | | 11773 | lvl17_nm | |
| 11773 | lvl18 | | 11773 | lvl18_nm | |
+-------+-----------+-------+-------+-----------+---------+
expected outcome:
+-------+-----------+-------+-------+-----------+---------+-----------+
| unit | variable1 | Value | unit2 | variable2 | value2 | Hierarchy |
+-------+-----------+-------+-------+-----------+---------+-----------+
| 11773 | lvl0 | 80000 | 11773 | lvl0_nm | thing 1 | Overflow |
| 11773 | lvl1 | 89000 | 11773 | lvl1_nm | thing 2 | Overflow |
| 11773 | lvl2 | 89022 | 11773 | lvl2_nm | thing 3 | Overflow |
| 11773 | lvl3 | 89756 | 11773 | lvl3_nm | thing 4 | Overflow |
| 11773 | lvl4 | 89833 | 11773 | lvl4_nm | thing 5 | Overflow |
| 11773 | lvl5 | 11773 | 11773 | lvl5_nm | | Overflow |
| 11773 | lvl6 | | 11773 | lvl6_nm | | Overflow |
| 11773 | lvl7 | | 11773 | lvl7_nm | | Overflow |
| 11773 | lvl8 | | 11773 | lvl8_nm | | Overflow |
| 11773 | lvl9 | | 11773 | lvl9_nm | | Overflow |
| 11773 | lvl10 | | 11773 | lvl10_nm | | Overflow |
| 11773 | lvl11 | | 11773 | lvl11_nm | | Overflow |
| 11773 | lvl12 | | 11773 | lvl12_nm | | Overflow |
| 11773 | lvl13 | | 11773 | lvl13_nm | | Overflow |
| 11773 | lvl14 | | 11773 | lvl14_nm | | Overflow |
| 11773 | lvl15 | | 11773 | lvl15_nm | | Overflow |
| 11773 | lvl16 | | 11773 | lvl16_nm | | Overflow |
| 11773 | lvl17 | | 11773 | lvl17_nm | | Overflow |
| 11773 | lvl18 | | 11773 | lvl18_nm | | Overflow |
+-------+-----------+-------+-------+-----------+---------+-----------+
Sample if first vlookup failed:
+--------+-------+-----------+
| Number | Name | Hierarchy |
+--------+-------+-----------+
| 82282 | Super | User |
+--------+-------+-----------+
mapping sheet
+-------+-----------+-------+-------+-----------+----------+
| unit | variable1 | Value | unit2 | variable2 | value2 |
+-------+-----------+-------+-------+-----------+----------+
| 96702 | lvl0 | 80000 | 96702 | lvl0_nm | thing 6 |
| 96702 | lvl1 | 80393 | 96702 | lvl1_nm | thing 7 |
| 96702 | lvl2 | 85176 | 96702 | lvl2_nm | thing 8 |
| 96702 | lvl3 | 82282 | 96702 | lvl3_nm | thing 9 |
| 96702 | lvl4 | 87927 | 96702 | lvl4_nm | thing 10 |
| 96702 | lvl5 | 88172 | 96702 | lvl5_nm | thing 11 |
| 96702 | lvl6 | 81025 | 96702 | lvl6_nm | thing 12 |
| 96702 | lvl7 | 96702 | 96702 | lvl7_nm | |
| 96702 | lvl8 | | 96702 | lvl8_nm | |
| 96702 | lvl9 | | 96702 | lvl9_nm | |
| 96702 | lvl10 | | 96702 | lvl10_nm | |
| 96702 | lvl11 | | 96702 | lvl11_nm | |
| 96702 | lvl12 | | 96702 | lvl12_nm | |
| 96702 | lvl13 | | 96702 | lvl13_nm | |
| 96702 | lvl14 | | 96702 | lvl14_nm | |
| 96702 | lvl15 | | 96702 | lvl15_nm | |
| 96702 | lvl16 | | 96702 | lvl16_nm | |
| 96702 | lvl17 | | 96702 | lvl17_nm | |
| 96702 | lvl18 | | 96702 | lvl18_nm | |
+-------+-----------+-------+-------+-----------+----------+
expected outcome:
+-------+-----------+-------+-------+-----------+----------+-----------+
| unit | variable1 | Value | unit2 | variable2 | value2 | Hierarchy |
+-------+-----------+-------+-------+-----------+----------+-----------+
| 96702 | lvl0 | 80000 | 96702 | lvl0_nm | thing 6 | |
| 96702 | lvl1 | 80393 | 96702 | lvl1_nm | thing 7 | |
| 96702 | lvl2 | 85176 | 96702 | lvl2_nm | thing 8 | |
| 96702 | lvl3 | 82282 | 96702 | lvl3_nm | thing 9 | User |
| 96702 | lvl4 | 87927 | 96702 | lvl4_nm | thing 10 | |
| 96702 | lvl5 | 88172 | 96702 | lvl5_nm | thing 11 | |
| 96702 | lvl6 | 81025 | 96702 | lvl6_nm | thing 12 | |
| 96702 | lvl7 | 96702 | 96702 | lvl7_nm | | |
| 96702 | lvl8 | | 96702 | lvl8_nm | | |
| 96702 | lvl9 | | 96702 | lvl9_nm | | |
| 96702 | lvl10 | | 96702 | lvl10_nm | | |
| 96702 | lvl11 | | 96702 | lvl11_nm | | |
| 96702 | lvl12 | | 96702 | lvl12_nm | | |
| 96702 | lvl13 | | 96702 | lvl13_nm | | |
| 96702 | lvl14 | | 96702 | lvl14_nm | | |
| 96702 | lvl15 | | 96702 | lvl15_nm | | |
| 96702 | lvl16 | | 96702 | lvl16_nm | | |
| 96702 | lvl17 | | 96702 | lvl17_nm | | |
| 96702 | lvl18 | | 96702 | lvl18_nm | | |
+-------+-----------+-------+-------+-----------+----------+-----------+
Your code,
SELECT
DISTINCT A.[unit],
B.[Hierarchy]
FROM [Mapping$] as A inner join [Segmentation$] as B ON (A.[unit2] = B.[Number] OR A.[Value] = B.[Number])
WHERE B.[Hierarchy] <> ''
Order By A.[unit] asc;
Since you are using Inner Join you don't need WHERE B.[Hierarchy] <> ''. The join will produce result only if there is a match.
to get your expected outcome, you could either use LEFT JOIN or subquery.
SELECT
DISTINCT A.[unit],
B.[Hierarchy]
FROM [Mapping$] as A **LEFT JOIN** [Segmentation$] as B ON (A.[unit2] = B.[Number] OR A.[Value] = B.[Number])
Order By A.[unit] asc;
This will produce list of Unique A.Unit and B.Hierarchy if available.
You can also use subqueries. (If segmentation.Number is not unique, use Top 1. Otherwise your subquery will return more than one row and complain about it)
SELECT
A.*,
(SELECT B.[Hierarchy]
FROM [Segmentation$] as B
WHERE (A.[unit2] = B.[Number] OR A.[Value] = B.[Number])) AS Hierarchy
FROM [Mapping$] as A
Order By A.[unit] asc;