Finding the max number from a list

[PritiOne]

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!

 

[PritiOne]

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!

 

[Aladin Akyurek]

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]

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
	A	B	C	D	E	F	G	H
1	NewProdList			ExpProdList			Vlookuplist
2	Item	Price		Item	Price		Item	Price
3	aaa	1		aaa	5		aaa	100
4	sss	2		ddd	200		ddd	200
5	aaa	3		sss	2		sss	300
6	aaa	4		fff	6		fff	400
7	aaa	5		ggg	7		ggg	500
8	fff	6
9	ggg	7
Sheet3