Differences

This shows you the differences between two versions of the page.

--- microsoft_excel:lookups:2_way_lookup [2021/08/04 13:40] – created peter
+++ microsoft_excel:lookups:2_way_lookup [2021/08/04 13:41] (current) – peter
@@ Line 1: / Line 1: @@
 ====== Microsoft Excel - Lookups - 2 way lookup ======
+Example data, with the "Jan" being in C2 and "Name" in G2.
+<code bash>
+	Jan	        Feb	        Mar		        Name	d
+a	0.83157866	0.178079914	0.66672199		Month	Feb
+b	0.570211314	0.006045599	0.359779573		Sales	0.165023538
+c	0.955030363	0.526567323	0.627424826
+d	0.643067459	0.165023538	0.619665299
+e	0.833736853	0.179510731	0.884165267
+f	0.19499946	0.756261052	0.220537328
+g	0.946088635	0.813338953	0.732952306
+h	0.212057033	0.954091809	0.808804201
+i	0.552254193	0.714799264	0.215011599
+</code>
+This returns "Sales" value based on both "Name" and "Month" values.
+----
+===== Using vlookup & match =====
+Formula to right of Sales is:
+<code>
+=VLOOKUP(H2,B3:E11,MATCH(H3,B2:E2,0),0)
+</code>
+----
+===== Using index & match =====
+Formula to right of Sales is:
+<code>
+=INDEX(C3:E11,MATCH(H2,B3:B11,0),MATCH(H3,C2:E2,0))
+</code>
+<WRAP info>
+'INDEX( , MATCH( , ,0))
+</WRAP>