VLOOKUP function

[Peters]

Hello,

I am encountering problems with de VLOOKUP function. I have a databas
with prices and company names. I have calculated the max. price pe
product and now i want the name of the company to appear in the nex
colum, how can i do that?

Pls help me!

Thx,

Pete

 

[Don Guillett]

try this where match,0 finds the 1st instance of max, match finds the row
and offset(row,whatever column)

=OFFSET(A1,MATCH(MAX(A:A),A:A,0),1)

 

[duane]

vlookup only works looking in left most column of data base for a valu
or text. It sounds like you need to look down the right hand colum
and find the name in the left hand column.

If the names are in a2:a100 and the prices are in b2:b100

=offset(b1,match(max(b2:b100),b2:b100,0),-1,1,1)

should find the row in which the max value resides and return the text
from the column 1 column to the left of that max valu

 

[Peters]

Thank you for responding so fast..

Iam going to specify it a little bit more, I have the names of th
company's horizontally ordered from a1:s1 and the prices from b7:s7
The products are in the most left colum. so now i want the company wit
the highest price to appear in colum T, is this possible?

Regards,

Pete

 

[duane]

=offset(a7,-(row(b7)-1),match(max(b7:s7),b7:s7,0),1,1)

this will return the value from row 1 corresponding to the maximu
vlaue in row 7 - note this formula could be copied to a different ro
(ie 6) and return the row 1 value based on the max in the new row (6

 

[Aladin Akyurek]

Let A4:B8 house:

{"Company","Price";
"S&A",90;
"ABC",88;
"Danton",77;
"Tri C",90}

C4: Rank

C5, copied down:

=RANK(B5,$B$5:$B$8)+COUNTIF(B5:$B$5,B5)-1

D1:

=MAX(B5:B8)

D2: 1 [ which means: Top 1]

D3:

=MAX(IF(INDEX(B5:B8,MATCH(D2,C5:C8,0))=B5:B8,C5:C8))-D2

which must be confirmed with control+shift+enter instead of just wit
enter.

D4: Top

D5, copied down:

=IF(ROW()-ROW($D$5)+1<=$D$2+$D$3,INDEX($A$5:$A$8,MATCH(ROW()-ROW($D$5)+1,$C$5:$C$8,0)),"")

The result/top 1 list that obtains is:

{"Top 1 List";
"S&A";
"Tri C"}

 

[Peters]

Hi

Thank you for your reactions, ive tried some formulas and Duane hi
formula seems to be working at my spreadsheet. Not all the answers ar
correct yet, but 70% is, so i will try to improve it till 100%, but i
u have any other suggestions feel free to react.

Thx a lot.

Pete