VLOOKUP function
 

Hello,

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

Pls help me!

Thx,

Peter


--
Peters
------------------------------------------------------------------------
Peters's Profile: http://www.excelforum.com/member.php...o&userid=16859
View this thread: http://www.excelforum.com/showthread...hreadid=320444

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)

--
Don Guillett
SalesAid Software

"Peters" wrote in message
...

Hello,

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

Pls help me!

Thx,

Peter


--
Peters
------------------------------------------------------------------------
Peters's Profile:
http://www.excelforum.com/member.php...o&userid=16859
View this thread: http://www.excelforum.com/showthread...hreadid=320444

vlookup only works looking in left most column of data base for a value
or text. It sounds like you need to look down the right hand column
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 value


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=320444

Thank you for responding so fast..

Iam going to specify it a little bit more, I have the names of the
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 with
the highest price to appear in colum T, is this possible?

Regards,

Peter


--
Peters
------------------------------------------------------------------------
Peters's Profile: http://www.excelforum.com/member.php...o&userid=16859
View this thread: http://www.excelforum.com/showthread...hreadid=320444

=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 maximum
vlaue in row 7 - note this formula could be copied to a different row
(ie 6) and return the row 1 value based on the max in the new row (6)


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=320444

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 with
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 Wrote:
Hello,

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

Pls help me!

Thx,

Peter


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=320444

Hi

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

Thx a lot.

Peter


--
Peters
------------------------------------------------------------------------
Peters's Profile: http://www.excelforum.com/member.php...o&userid=16859
View this thread: http://www.excelforum.com/showthread...hreadid=320444