Lookup with INDEX and MATCH formulas

[Antje Crawford]

Hello,
I have the following formula (I apologize for its
length) ...

INDEX(Yields!$A$5:$A$65536,MATCH(LARGE((Yields!D$11,Yields!
D$14,Yields!D$30,Yields!D$34,Yields!D$37,Yields!
D$44,Yields!D$50,Yields!D$59,Yields!D$63,Yields!
D$68,Yields!D$76,Yields!D$79,Yields!D$87,Yields!
D$91,Yields!D$95,Yields!D$103,Yields!D$106,Yields!
D$113,Yields!D$116,Yields!D$127,Yields!D$132,Yields!
D$144,Yields!D$148,Yields!D$153),1),Yields!
$D$5:$D$65536,0))

This formula works almost the way I would like. The only
problem I have encountered is that it will return all
items from column A on the Yields sheet.
But I would like for it to only return labels and
corresponding values if the cells in column A (those are
the labels) contain the word "Total". I tried to add the
FIND function but was not successful in extracting the
correct arguments.
Can anybody help please?
All suggestions and solutions are greatly appreciated.
TIA.
Best Regards,
Antje Crawford

 

[Arvi Laanemets]

Hi

To give an exact answer, I have to know in which column are labels and
values.

In general, I think the formula must be something like
=INDEX(Yields!$A$5:$C$65536,MATCH(...),n)
where you fill MATH(...) as in your formula, and where n will be index of
return value column in range Yields!$A$5:$C$65536.
I.e. when labels are in column B and vales in column C, then with n=2 the
formula will return label, and with n=3 value is returned.

 

[Jason Morin]

This will return the maximum value in col. D for those
rows (as specified in your formula) that contain "total"
in col. A:

=MAX(IF((ISNUMBER(SEARCH("total",Yields!A1:A153)))*(OR(ROW
(Yields!D1153)=
{11,14,30,34,37,44,50,59,63,68,76,79,87,91,95,103,106,113,1
16,127,132,144,148,153})),Yields!D1153))

Array-entered (press ctrl/shift/enter).

Watch the wrap when you copy.

HTH
Jason
Atlanta, GA

 

[Antje Crawford]

Hello Jason,
this formula is working very nicely, extracting the
values. How can this formula be adjusted to return the
label from col. A that corresponds to the maximum value in
col. D?

Thanks for your help.
BR, Antje Crawford