Match 2 columns import 3rd

[Nichola_Lally]

Hi,

Please help as I am so confused. I need to match 2 fields (branch number and distance) and if these two values are matched exactly (as the list contains multiple distances per branch) bring over a third column from another sheet which would show which group the branch belonged too.

My data is vast so I tried doing a pivot table thouigh unfortunately as text cannot be bought over in the data part of the table I cannot get it to show the detail I need.

I know a Match and VLookup could also be used though I have attempted to follow the previous posts but the formula does not appear to work for me.

Very stuck, if you coudl help please let me know.

Many Thanks in advance - Nichola

[zbor]

Hi Nichola, can you upload example?

[NBVC]

Something like:

=INDEX(Sheet2!$C$1:$C$1000,MATCH(1,INDEX((Sheet2!$A$1:$A$100=A1)*(Sheet2!$B$1:$B$100=B1),0),0))

Where Sheet2!A1:C100 contain database

And A1 and B1 of current sheet contain lookup items.

Adjust ranges and references to sheets and cells as required

Copy formula down.

[Nichola_Lally]

Here is a small sample of the data with the desired outcome on the left.

Hope this helps

NVBC - Thanks I will go through this now to hope I can make sense of it!

Thank you for my quick responses already!

[NBVC]

So are you looking up Distance Rank # that is greater than or equal to the value in H2?

If yes, formula:

=INDEX($D$2:$D$33,MATCH(1,INDEX(($A$2:$A$33=$G2)*($B$2:$B$33>=H2),0),0))

but, in L3 you have a number that is bigger than any number in column B? How is that to be handled?

[Nichola_Lally]

Sorry if thats not very clear the data I need to organise is columns a - d. I used f - m to show the correct data i need the formule to show.

I need to show for the closest three colour groups to each branch number (with distances provided).

[NBVC]

Maybe you are looking for this in I2:

=INDEX($D$2:$D$33,MATCH(1,INDEX(($A$2:$A$33=$G2)*(ROUND($C$2:$C$33,2)=H2),0),0))

copy to other cells where you need formula applied.

[Nichola_Lally]

Ok I have attached the data source.

For each branch number I have identified multiple sites and their distances (Rank Distance, Distance columns) and then colour system used at the closest sites (column called group).

Obviously for the data attached branch 1 results should read:

BRANCH - DISTANCE RANK - DISTANCE - GROUP/SYSTEM COLOUR
1 1 0 Blue
1 2 1.732729418 Purple
1 3 2.47085187 Purple

This is easily viewed I however thousands of branches to do this for.

My plan of attack was to simply put all of this into a pivot table which would have the branch number along the y axis and then distance ranking along the x so that the group and distnace fields could be dropped into the main data section. The problem with this is that only numbers not text can be used in the middle of a pivot tabel. If I try to rewwork this there is too much data to see the fields properly.

So then i thought I could arrange this as a pivot table without the group in. So this would list each branch and then the three closest distances into a table. I hoped I could then use a VLookup and If statement to basically say....if the branch number = 1 and the distance=0 then return the group blue which would be the case in the example above.

[NBVC]

See attached.

You would need to make a unique list of Branches in y-axis

and unique Distance Ranks (but 2 of each)

Then in G4:

Please Login or Register  to view this content.

and in H4:

Please Login or Register  to view this content.

copy both down.

Then copy those 2 columns and paste to each other Distance/Group columns.