Vlookup with IF

[Pumperkin]

Hi,

I am trying to do a Vlookup with IF, but just get a 0 answer rather than the result of the VLOOKUP.

I am trying to find (from a long list) the most expensive product in stock (the list includes in stock and sold items). I have a MAX(IF) array in cell K7 to find the highest value and am trying to find which product has that value.

The data itself is on a different sheet - the stock status is in Column 13 with the product details in Column 16 (however, the VLOOKUP starts in column 6 hence the number shifting).

I have the following formula:

=IF(Table3[Despatch Time]="Not Despatched", VLOOKUP(K7, Table3[[Cost]:[Model/Memory]], 11, ),)

Any ideas?

 

[etaf]

VLOOKUP(K7, Table3[[Cost]:[Model/Memory]], 11, )
so thats TRUE default and gets the nearest value,
Whats the range of Table3[[Cost]:[Model/Memory]] , the column number uses that as the reference
So 11 columns from [Cost] column

if you just use
VLOOKUP(K7, Table3[[Cost]:[Model/Memory]], 11, )
What do you get ?

I would have thought you needed
VLOOKUP(K7, Table3[[Cost]:[Model/Memory]], 11, FALSE)
OR
VLOOKUP(K7, Table3[[Cost]:[Model/Memory]], 11, TRUE) for the nearest vlaue

 

[Pumperkin]

VLOOKUP(K7, Table3[[Cost]:[Model/Memory]], 11, )
so thats TRUE default and gets the nearest value,
Whats the range of Table3[[Cost]:[Model/Memory]] , the column number uses that as the reference
So 11 columns from [Cost] column

if you just use
VLOOKUP(K7, Table3[[Cost]:[Model/Memory]], 11, )
What do you get ?

I would have thought you needed
VLOOKUP(K7, Table3[[Cost]:[Model/Memory]], 11, FALSE)
OR
VLOOKUP(K7, Table3[[Cost]:[Model/Memory]], 11, TRUE) for the nearest vlaue

Hi, Thanks for your reply. If I use the first formula I get the product with the highest value of all time, whether it's in stock or not. What I want to do is have an IF conditioner in there so it will only return the value of the most expensive product in stock (this is determined by "Despatch Time", row 8 from Cost, being "Not Despatched").

 

[etaf]

why not use a MAX & IF , or if you have office 365 installed you can use MAXIFS()
do you have office 365
otherwise see here MAX with 2 criteria

Maximum if multiple criteria

To get the maximum value in a set of data that meets multiple criteria, you can use a formula based on the MAXIFS function. In the example shown, the formula in H5 is: =MAXIFS(data[Value],data[Group],F5,data[Temp],"

exceljet.net

 

[Pumperkin]

why not use a MAX & IF , or if you have office 365 installed you can use MAXIFS()
do you have office 365
otherwise see here MAX with 2 criteria

Maximum if multiple criteria

To get the maximum value in a set of data that meets multiple criteria, you can use a formula based on the MAXIFS function. In the example shown, the formula in H5 is: =MAXIFS(data[Value],data[Group],F5,data[Temp],"

exceljet.net

Hi, I have Office 2016.

I already have

=MAX(IF(Table3[Despatch Time]="Not Despatched",Table3[Cost]))

As an array formula which returns the value £500.

What I want to do is find the product that is in stock that has that value.

 

[etaf]

did you look at the link, which shows how to use 2 criteria
{=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values)))}

So "not dispatched' and whatever K7 is

not sure if the table names are correct BUT, something like
=MAX ( IF( Table3[Despatch Time]="Not Despatched", IF ( Table3[[Cost] = K7 , Table3[Model/Memory] )))

 

[Pumperkin]

did you look at the link, which shows how to use 2 criteria
{=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values)))}

So "not dispatched' and whatever K7 is

not sure if the table names are correct BUT, something like
=MAX ( IF( Table3[Despatch Time]="Not Despatched", IF ( Table3[[Cost] = K7 , Table3[Model/Memory] )))

Hi, Sorry that seemed to appear after I'd started typing my reply. I tried that (table names are right) but that just gave a 0 value. Not sure if that's right because I'm not finding the maximum value of the product make/model, but rather I'm finding the make/model with the max value in stock (so I need VLOOKUP I'd have thought?).

 

[etaf]

ok, I did not understand the requirement
have a read here,

Judi Togel Agen Pasang Togel Online Terpercaya Bayaran Termahal

Judi Togel Situs pasang taruhan togel online terbesar dengan bayaran termahal banyak diskon, daftar judi togel online disitus terpercaya dengan jackpot besar

excelxor.com

NOT sure how to translate your table names to the range
but i have an example here
BUT as you can see if you have 2 values that match you get the 1st one

Book1
	A	B	C	D	E
1	Product	Stock Status	Value
2	Item2	out of stock	1600		Item14
3	Item3	out of stock	2000
4	Item4	out of stock	2400
5	Item5	out of stock	2800
6	Item6	out of stock	3200
7	Item7	out of stock	3600
8	Item8	out of stock	4000
9	Item9	out of stock	4400
10	Item10	In Stock	4800
11	Item11	out of stock	5200
12	Item12	out of stock	5600
13	Item13	out of stock	6000
14	Item14	IN Stock	6400
15	Item15	In stock	6400
Sheet1

Cell Formulas
Range	Formula
E2	E2	=INDEX(A2:A100,MATCH(MAX(IF(B2:B100="in stock",C2:C100)),IF(B2:B100="in stock",C2:C100),0))

 

[Pumperkin]

Hi, Looks perfect will have a try and let you know Thanks for all your help

 

[Pumperkin]

Hmm I now get an error #N/A with Value not available.

I copied and pasted your formula but amended the parts as required:

=INDEX(Table3[Model/Memory],MATCH(MAX(IF(Table3[Despatch Time]="Not Depatched",Table3[Cost])),IF(Table3[Despatch Time]="Not Despatched",Table3[Cost]),0))

Think I'm getting closer...