index Match, or Vlookup Match..

[news.transedge.com]

Sure to be easy for most, yet difficult for me..

I want to perform a price sheet lookup, the catch is I need to match to
fields.

Column A Column B Price1 Price2
City name State $$ $$


The price sheet has 15,000 names of cities, many duplicate, but become
unique when you combine the state.

How would you recomend I do this?

Can you provide an example, please?

Thank you,

John

 

[JMB]

Assuming the data is in A210, this should return Price1.

=Index(C2:C10, MATCH(1, (A2:A10="City")*(B2:B10="State"),0))
array entered w/Cntrl+Shift+Enter

or, as long as the combination of city/state will always be unique, you
*could* use
=SUMPRODUCT(--(A2:A10="City"), --(B2:B10="State"), C2:C10)

but if there ever happen to be duplicates, sumproduct wil return the total
for every match it finds where Index/Match will return just the first match
it finds. Some folks shy away from sumproduct when they want to perform a
lookup and not a count or a sum of all matching records (although you could
add
=SUMPRODUCT(--(A2:A10="City"), --(B2:B10="State"))
in an adjacent cell as a check to verify there is only one matching record
and perhaps some conditional formatting to flag instances where there is more
than one unique combination).