How do i a return value from one column using multiple criteria on others?

**SteveW68** · 03-20-2010, 09:54 AM

Hi all,

I have a table with 4 columns and i'm trying to return the value from col C where columns A+B contain certain values/names
There can only be one row containing the same 2 entries in A,B however they could be in either col.
The table also contains zero values but no blank rows/cols

ie in the table below i would need to retrieve the price or total for London+Rome and London+Paris

City1 City2 Price Total
London Berlin 120 1
Berlin Paris 50 2
Paris London 25 5
Berlin Rome 95 4
Rome Paris 80 3
London Rome 65 0
Rome Berlin 75 4

cheers
Steve

**Palmetto** · 03-20-2010, 10:24 AM

Here are a couple of ways:

=SUMPRODUCT(--(B2:B8=B10),--(C2:C8=C10),(D2:D8))

or using a concatenated look up value

=VLOOKUP(A10,A2:D8,4,0)

See attached.

**Everstrivin** · 03-20-2010, 10:27 AM

Here is a solution using SUMPRODUCT. The formulas will take any amount of data in any of the 4 columns, however it will only work if column A has the most rows of data, so if you had any single locations put that location in column A. You can see in the file I've attached.

**SteveW68** · 03-20-2010, 10:36 AM

hi there, thanks for replying

the problem i have is that i dont know which columns the values are in

ie i need to find London+Paris but London could be in either A or B and so could Paris

the table is dynamic so today London could be in A, Paris in B and next week the other way round

If i concatenate LondonParis and ParisLondon one will return a zero value - which is a possible outcome in the total field

Also fields that i need to search for are generated by another sheet which will ask for London+Paris or Rome+Berlin depending on other outcomes so the formula needs to link to other cells for the input

An arrayed Sum sort of works =SUM(($A$3:$A$50=$V2)*($B$3:$B$50=$V3)*$C$3:$C$50) but only allows for 1 varient of the 2 cities (V2,V3) but again it only allows for 1 varient of the 2 cities and to allow for all combinations for up to 50 cities is looking a nightmare

**Palmetto** · 03-20-2010, 10:47 AM

If they are not going to consistently be in the same column, then perhaps use this:

=IF(MATCH(B10&C10,A2:A8,0)>0,VLOOKUP(B10&C10,A2:D8,4,0),VLOOKUP(C10&B10,A2:D8,4,0))

**Everstrivin** · 03-20-2010, 11:07 AM

Did you look at what I uploaded? In my response it doesn't matter what order the cities are in and one can even be blank if required.

**SteveW68** · 03-20-2010, 11:11 AM

thanks a lot - thats working fine

now i've got to extract some other info from a similiar report from the same original data

City1 City2 Price1 Price2
London Berlin 120 90
Berlin Paris 100 105
Paris Rome 80 65 etc etc

i'd need to produce something that says for Berlin+London, London=120 and Berlin=90

Again the 2 cities could be in either A,B but both can only be in one row

**Palmetto** · 03-20-2010, 11:23 AM

The last solution I suggested works regardless if there are one or two cities and it doesn't require use of the volatile OFFSET function. I've upload a revised workbook in my earlier post to demonstrate.

Volatile functions automatically force Excel to re-calculate when opening the workbook or when making ANY change on the sheet. Even if you make no changes, Excel will prompt you to save the workbook.