How to integrate VLOOKUP with MAX function?

[rajamdade]

Hello everyone,

Kindly let me know how to integrate the VLOOKUP function with MAX in order to format data of Table 1 for the given query ids as in Table 2. Moreover, I would appreciate it if you could help with the formula to populate the Max identity value in the neighboring cell:

MS Office Version: 2016
Provided data: Table 1.

Query	Identification	% identity
28097.m000052	MAP kinase 9	80.658
28097.m000052	MAP kinase 16	79.254
28752.m000329	MAP kinase 11	81.481
28752.m000329	MAP kinase 4	77.746
29625.m000700	MAP kinase 18	38.944
29625.m000700	MAP kinase homolog 2	38.762
29625.m000700	MAP kinase 19	38.523

Required output format: Table 2.

Query	Exact Identification	MAX % identity
28097.m000052	MAP kinase 9	80.658
28752.m000329	MAP kinase 11	79.254
29625.m000700	MAP kinase 18	38.944

 

[jasonb75]

Vlookup is not the correct function for the task. With a newer version of Excel, you could use MAXIFS. With your version, the best you will get will be something like

Book1 (version 1).xlsb
	A	B	C
1	Query	Identification	% identity
2	28097.m000052	MAP kinase 9	80.658
3	28097.m000052	MAP kinase 16	79.254
4	28752.m000329	MAP kinase 11	81.481
5	28752.m000329	MAP kinase 4	77.746
6	29625.m000700	MAP kinase 18	38.944
7	29625.m000700	MAP kinase homolog 2	38.762
8	29625.m000700	MAP kinase 19	38.523
9
10
11	Query	Exact Identification	MAX % identity
12	28097.m000052	MAP kinase 9	80.658
13	28752.m000329	MAP kinase 11	81.481
14	29625.m000700	MAP kinase 18	38.944
Sheet11

Cell Formulas
Range	Formula
C12:C14	C12	=AGGREGATE(14,6,$C$2:$C$8/($A$2:$A$8=A12)/($B$2:$B$8=B12),1)

 

[rajamdade]

Vlookup is not the correct function for the task. With a newer version of Excel, you could use MAXIFS. With your version, the best you will get will be something like

Book1 (version 1).xlsb
	A	B	C
1	Query	Identification	% identity
2	28097.m000052	MAP kinase 9	80.658
3	28097.m000052	MAP kinase 16	79.254
4	28752.m000329	MAP kinase 11	81.481
5	28752.m000329	MAP kinase 4	77.746
6	29625.m000700	MAP kinase 18	38.944
7	29625.m000700	MAP kinase homolog 2	38.762
8	29625.m000700	MAP kinase 19	38.523
9
10
11	Query	Exact Identification	MAX % identity
12	28097.m000052	MAP kinase 9	80.658
13	28752.m000329	MAP kinase 11	81.481
14	29625.m000700	MAP kinase 18	38.944
Sheet11

Cell Formulas
Range	Formula
C12:C14	C12	=AGGREGATE(14,6,$C$2:$C$8/($A$2:$A$8=A12)/($B$2:$B$8=B12),1)

Thanks, @jasonb75 I actually missed something in my question, I also wanted to return the value of "Identification" in table 1 to the "Exact identification" of table 2 having a max identity as in column C.

Thanks again...

 

[jasonb75]

Is this what you mean?

Book1 (version 1).xlsb
	A	B	C
1	Query	Identification	% identity
2	28097.m000052	MAP kinase 9	80.658
3	28097.m000052	MAP kinase 16	79.254
4	28752.m000329	MAP kinase 11	81.481
5	28752.m000329	MAP kinase 4	77.746
6	29625.m000700	MAP kinase 18	38.944
7	29625.m000700	MAP kinase homolog 2	38.762
8	29625.m000700	MAP kinase 19	38.523
9
10
11	Query	Exact Identification	MAX % identity
12	28097.m000052	MAP kinase 9	80.658
13	28752.m000329	MAP kinase 11	81.481
14	29625.m000700	MAP kinase 18	38.944
Sheet11

Cell Formulas
Range	Formula
B12:B14	B12	=INDEX($B$2:$B$8,MATCH(2,1/($A$2:$A$8=A12)/($C$2:$C$8=C12)))
C12:C14	C12	=AGGREGATE(14,6,$C$2:$C$8/($A$2:$A$8=A12),1)

 

[rajamdade]

Is this what you mean?

Book1 (version 1).xlsb
	A	B	C
1	Query	Identification	% identity
2	28097.m000052	MAP kinase 9	80.658
3	28097.m000052	MAP kinase 16	79.254
4	28752.m000329	MAP kinase 11	81.481
5	28752.m000329	MAP kinase 4	77.746
6	29625.m000700	MAP kinase 18	38.944
7	29625.m000700	MAP kinase homolog 2	38.762
8	29625.m000700	MAP kinase 19	38.523
9
10
11	Query	Exact Identification	MAX % identity
12	28097.m000052	MAP kinase 9	80.658
13	28752.m000329	MAP kinase 11	81.481
14	29625.m000700	MAP kinase 18	38.944
Sheet11

Cell Formulas
Range	Formula
B12:B14	B12	=INDEX($B$2:$B$8,MATCH(2,1/($A$2:$A$8=A12)/($C$2:$C$8=C12)))
C12:C14	C12	=AGGREGATE(14,6,$C$2:$C$8/($A$2:$A$8=A12),1)

Thanks, @jasonb75 but I am getting the following error in the "exact identification" column...

Ricinus_MAPKs Domain-Motif Scan.xlsx
	A	B	C
1	Query	Identification	% identity
2	28097.m000052	MAP kinase 9	80.658
3	28097.m000052	MAP kinase 16	79.254
4	28752.m000329	MAP kinase 11	81.481
5	28752.m000329	MAP kinase 4	77.746
6	29625.m000700	MAP kinase 18	38.944
7	29625.m000700	MAP kinase homolog 2	38.762
8	29625.m000700	MAP kinase 19	38.523
9
10
11	Query	Exact Identification	MAX % identity
12	28097.m000052	#VALUE!	80.658
13	28752.m000329		81.481
14	29625.m000700		38.944
Sheet5

Cell Formulas
Range	Formula
B12	B12	=INDEX($B$2:$B$8,MATCH(2,1/($A$2:$A$8=A12)/($C$2:$C$8=C12)))
C12:C14	C12	=AGGREGATE(14,6,$C$2:$C$8/($A$2:$A$8=A12),1)

 

[jasonb75]

Try array confirming the formula, select B12, then press F2, Shift +Ctrl + Enter.

 

[rajamdade]

Try array confirming the formula, select B12, then press F2, Shift +Ctrl + Enter.

Thank you @jasonb75 it worked... ?