Finding the max number from a list

PritiOne

Active Member
Joined
Jun 2, 2004
Messages
357
Ok -

I have a sheet called Std Detail. On that sheet is a defined range: NewProdList

I have another sheet called Exp Detail. On that sheet is a defined range:
ExpProdList

On the Std Detail sheet each item in NewProdList has a price in the next column (say NewprodList is in column b, and the list price is in column c). By the way the list price is pulled in using a vlookup.

I need to know how, on the Exp Detail sheet, if I have a product listed in ExpProdList, do I search for the highest price for that item in NewProdList, and have that price listed. If the product is not in NewProdList, then I need the vlookup formula to kick in.

Sounds complicated! I am stuck...

Thanks!
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

PritiOne

Active Member
Joined
Jun 2, 2004
Messages
357
This is where I am so far, I kind-of get the right result, but not really.

This formula doesn't let me evaluate the match separately from the vlookup.

Help - PA-LEASE!

:rolleyes:
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Describe NewProdlist on Std Detail:

How many columns does this table have?

What kind of data do you have in each column of this table?

Describe ExpProdList on Exp Detail:

How many columns does this table have?

What kind of data do you have in each column of this table?

Specify the lookup cell (cell address) on Exp Detail.

You state: "I need to know how, on the Exp Detail sheet, if I have a product listed in ExpProdList, do I search for the highest price for that item in NewProdList, and have that price listed. If the product is not in NewProdList, then I need the vlookup formula to kick in."

The first part seems clear, the last bit about a vlookup formula kicking in is not.

BTW, how is your "lookup arrays" thread ended?
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Try:

=IF(ISNUMBER(MATCH(D3,$A$3:$A$9,0)),MAX(IF($A$3:$A$9=D3,$B$3:$B$9)),VLOOKUP(D3,$G$3:$H$7,2,0))

must be entered with Ctrl + shift + enter in E3 before being dragged down.
Book1
ABCDEFGH
1NewProdListExpProdListVlookuplist
2ItemPriceItemPriceItemPrice
3aaa1aaa5aaa100
4sss2ddd200ddd200
5aaa3sss2sss300
6aaa4fff6fff400
7aaa5ggg7ggg500
8fff6
9ggg7
Sheet3
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,328
Members
414,054
Latest member
Sameer50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top