Excel Formula

Nefarious · 29 Apr 2018 at 17:50

I have three columns of values and I want to pull out specific values from them.

The first two columns are essentially X, Y coordinates and the third column is the actual 'data'.

How do I pull the values from the third column that match up with my 2nd list of XY that are in an entirely different order?

Nefarious · 29 Apr 2018 at 18:01

So far, I've gotten something like this:

=INDEX(data,MATCH(TRUE,EXACT(G2&H2,A2&A2:B113&B113),0),4)

Am I close?

Grrrrr · 30 Apr 2018 at 14:55

Can you post a screenshot?

BigT · 1 May 2018 at 09:36

If your two columns with differently ordered co-ordinates are in G and H, and your original data is in B , C and D where B and C are your coordinates and are being matched against G and H and D is your data you want to return then:

Create a column A where A2=B2&C2

this creates a concatenated X/Y coordinate value

Then in column I (or wherever you want your match to be returned):

=VLOOKUP(G2&H2,A

,4,FALSE)

That says scan the table comprised of columns A to D, find the first occurrence (which will be in Column A) of your concatenated G/H co-ordinate and then return the value in the matching row from the fourth column (D)

But a snapshot of data in a screenshot will help a lot.

BigT · 1 May 2018 at 09:37

That grinning face is a colon followed by D btw. I don't know how to escape forum smilies!