Value based cell matching range

[Guest]

Hi,
Hoping that someone out there can help.
formula is contingent on multiple possible variances and not sure on how to
write. formula is in cell A15 and should do the following:
If A1 = B1:B20 (value in a range), then the adjacent cell value to the
range to be used as variable within the formula.
=if(a1=z1:z20),b1*adjacent cell value to z1:z20

Im new at this and apologize for poor description, but not sure how to be
clearer. Easier to envision than describe.

Thanks

 

[Biff]

Hi Steven!

=if(a1=z1:z20),b1*adjacent cell value to z1:z20

So, if A1=Tom, and Tom is in Z10, multiply B1 by the value
in AA10?

When you say "the adjacent cell value to the range" and
the range is Z1:Z20, then the adjacent cell value is in
col AA.

Is this correct?

Also, you didn't specify what to do if A1 does not equal
Z1:Z20.

Maybe something like this:

=B1*VLOOKUP(A1,Z1:AA20,2,0)

Need more info!

Biff

 

[Frank Kabel]

Hi
really not so clear what you're trying to do
Best thing would be if you could post some example rows of data (plain
text - no attachments please) and describe b ased on your example what
your expected result is

 

[Guest]

Thanks Biff & Frank for you quick responses.
I'm in logistics biz and spreadsheet are details of shipments.
each row pertains to an individual shipment.
A1 = shipment destination
b1 = weight of shipment
z1:z20 = destinations
aa1:aa20 = rate/kg ( rate per kilo to destinations adjacent cell in column z.)

I want the formula to match up destination in its row with corresponding
dest. in range z1:z20 then multiply weight times rate in adjacent aa1:aa20.

Hope this gives a clearer understanding.

Brgds from Dallas

 

[Guest]

Specifically so
if A1=FRA, and FRA is in Z10, multiply B1 (100 KG) by the value in AA10
($1.00)

A1 will always equal a value withing range Z1:Z20
Thanks Kindly

 

[Biff]

Hi Steven!

Ok, then use the formula I offered with this slight
modification:

=IF(OR(A1="",B1=""),"",B1*VLOOKUP(A1,Z1:AA20,2,0))

This will leave A15 "blank" if either A1 or B1 is empty.
Since you said the lookup value will always be in the
lookup range there's really no reason for error trapping.
(although it would make things "bullet proof" !

Biff

 

[Aladin Akyurek]

=B1*INDEX($AA$1:$AA$20,MATCH(A1,$Z$1:$Z$20,0))

Thanks Biff & Frank for you quick responses.
I'm in logistics biz and spreadsheet are details of shipments.
each row pertains to an individual shipment.
A1 = shipment destination
b1 = weight of shipment
z1:z20 = destinations
aa1:aa20 = rate/kg ( rate per kilo to destinations adjacent cell in column z.)

I want the formula to match up destination in its row with corresponding
dest. in range z1:z20 then multiply weight times rate in adjacent aa1:aa20.

Hope this gives a clearer understanding.

Brgds from Dallas

 

[Frank Kabel]

Hi
use
=B1*VLOOKUP(A1,Z1:AA20,2,0)

 

[Guest]

Thanks everyone, Aladin / Biff both of your solutions worked.
Best Regards,
Steven