User Tools

Site Tools


microsoft_excel:lookups:2_way_lookup

Microsoft Excel - Lookups - 2 way lookup

Example data, with the “Jan” being in C2 and “Name” in G2.

	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			

This returns “Sales” value based on both “Name” and “Month” values.


Using vlookup & match

Formula to right of Sales is:

=VLOOKUP(H2,B3:E11,MATCH(H3,B2:E2,0),0)

Using index & match

Formula to right of Sales is:

=INDEX(C3:E11,MATCH(H2,B3:B11,0),MATCH(H3,C2:E2,0))

'INDEX( , MATCH( , ,0))

microsoft_excel/lookups/2_way_lookup.txt · Last modified: 2021/08/04 13:41 by peter

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki