Vlookup max value and finding number in specific column

[Nic7]

Hi, thanks for help. 1. I am trying to find a max number in a vlookup but my formula is not working (see below)

101 101 3 96 78

So if im trying to find 101 in the table i would do =VLOOKUP,101,table,3,false) but it will bring up 78 but what i need it to do is bring up the largest value in the table which is 96 (without using 2 as the column index) Is there a MAX forumla that can be inserted.

2. Also if i need to get the heading in a column what formula can i use (see below)

APPLE PEARS ORANGES no. fruit
3 5 7 3 (what formula can i use so that when in column no. it sees 3 it will put 'Apples' in column fruit.
4 6 2 6


thanks in advance

 

[Joe4]

Welcome to the Board!

101 101 3 96 78

So if im trying to find 101 in the table i would do =VLOOKUP,101,table,3,false) but it will bring up 78 but what i need it to do is bring up the largest value in the table which is 96 (without using 2 as the column index) Is there a MAX forumla that can be inserted.

That is a very confusing explanation that does not seem to make any sense to me. The largest value in that range is 101, not 96.
If you want to find the maximum value of a range of cells, you can just use the MAX function. See: MAX Function

If that is not what you want, please try posting a sample of your actual data and expected results.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

For part 2. if you have a set-up like this:

The formula in cell G2 would look like this:

=INDEX(A1:C1,MATCH(F2,A2:C2,0))

 

[Nic7]

Welcome to the Board!


That is a very confusing explanation that does not seem to make any sense to me. The largest value in that range is 101, not 96.
If you want to find the maximum value of a range of cells, you can just use the MAX function. See: MAX Function

If that is not what you want, please try posting a sample of your actual data and expected results.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

For part 2. if you have a set-up like this:
View attachment 101071

The formula in cell G2 would look like this:

=INDEX(A1:C1,MATCH(F2,A2:C2,0))

Thanks so much! Second part worked.
This is what i mean for the 1st part - I want to look up ID 101 and see what is the largest amount in that row ( so can i do a vlookup that will give me the max number which in ID 101 is 8

 

[Alex Blakenburg]

Ideally we prefer to have some data we can work with using the XL2BB tool. If you use a picture it helps if you give the row and column references as you will see in @Joe4's image.

Does either the Index or Filter option in the below work for you ?

Book3
	A	B	C	D	E	F	G	H	I
1	ID NO	Max				ID NO	Amount
2	101	8	Filter			101	6	8	5
3	102	7	Index			102	4	7	2
4	103					103	7	8	2
5	104					104	8	2	4
Sheet1

Cell Formulas
Range	Formula
B2	B2	=MAX(FILTER($G$2:$I$4,$F$2:$F$4,0))
B3	B3	=MAX(INDEX($G$2:$I$4,MATCH(A3,$F$2:$F$4,0),0))

 

[Joe4]

As long as you are using Excel 365:

Formula to put in cell B2 and copy down:

=MAX(FILTER(E$2:G$5,D$2:D$5=A2))

EDIT: Whoops, I had stepped away in the middle of posting, and didn't see that Alex posted in the meantime.
He also shows a good way to do it if you are not using 365.

 

[Nic7]

Welcome to the Board!


That is a very confusing explanation that does not seem to make any sense to me. The largest value in that range is 101, not 96.
If you want to find the maximum value of a range of cells, you can just use the MAX function. See: MAX Function

If that is not what you want, please try posting a sample of your actual data and expected results.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

For part 2. if you have a set-up like this:
View attachment 101071

The formula in cell G2 would look like this:

=INDEX(A1:C1,MATCH(F2,A2:C2,0))

Thanks so much all worked !

 

[Joe4]

You are welcome.
Glad we were able to help.