Problems with lookup functions

  • Thread starter Thread starter Chris-C
  • Start date Start date
C

Chris-C

Hi,

I have a row of values, for example prices from several shops, with th
product in the left hand column and the shop in the top row. In th
right hand columns I have the minimum value and in the column next t
that I want the name of the shop where the product is cheapest. I hav
tried a variation of hlookup, vlookup, match etc. to try and find som
way of combining them o get what I want but the only way I have foun
to work is using if functions which are long, laborius and prone t
mistakes!
I have attached a sample of what I mean if i helps.

Can anyone help please??

Chri
 
Hi Chris
if the mimimum value is in column G try the following formula in H2
(the first data row
=INDEX($B$1:$F$1,1,MATCH(H2,B2:F2,2))
and copy down
 
Hi Chris

couldn't find the attachment so i hope i've understood you correctly .. if
you shops are in row 1 and your products are in column A (starting at row 2)
, and the min value is in column H (first one at H2) then this should give
you what you're after

=INDEX($B$1:$G$1,,MATCH(H2,B2:G2,0))

Hope this helps
Cheers
JulieD
 
Chris,

assume the shops are in B1:Z1, the products in column A and you want the
shop name
for the min in row 2 B2:Z2

=INDEX($B$1:$Z$1,MATCH(MIN($B2:$Z2),$B2:$Z2,0))

copy down to get the rest of the min values

Will return the first hit if there are more than one min value
 
Back
Top