Match Multiple Lookup Array

Question

Currently working on my final project thesis, i need some excel calculation for manual analytic section on the report.

So here the problem,

I need to define position of each facelets.

In example position Facelets 1 is 9. (Check picture below)

So far, i get the way to reach that by transpose each row to 1 column. Like picture below and use excel function MATCH() to get the relative position.

=MATCH(1;A1:A18;0)

Is there a way to get relative position without transpose the lookup array?

I have try use multiselect row (example A1:A3;B1:B3;), but it return errors.

Hope someone can help me.

do you have the Key somewhere? are the values always unique? — Scott Craner
– Scott Craner, Commented May 29, 2018 at 21:16
@ScottCraner yes it is... the value always unique... i just need the position of each facelets... Reference of position is at the first image, the right one (with gray background) — Yohanes Isa Ananto Tri Pratiwi
– Yohanes Isa Ananto Tri Pratiwi, Commented May 29, 2018 at 21:19

Scott Craner · Accepted Answer · 2018-05-29 21:26:35Z

2

Using a "Key" to return the correct location:

=INDEX($N$1:$Y$9,
   AGGREGATE(15,6,(ROW($A$1:$L$9)-MIN(ROW($A$1:$L$9))+1)/($A$1:$L$9=AB2),1),
   AGGREGATE(15,6,(COLUMN($A$1:$L$9)-MIN(COLUMN($A$1:$L$9))+1)/($A$1:$L$9=AB2),1))

As long as the two areas are the same size and the values unique this will return the location on the "Key"

answered May 29, 2018 at 21:26

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yohanes Isa Ananto Tri Pratiwi Over a year ago

hi, thanks for your answer Scott. I've tried your formula and got an error. Is there something wrong with my pasted formula? imgur.com/a/2sajz9l

Scott Craner Over a year ago

@YohanesIsaAnantoTriPratiwi you may need to change all the , to ; for your local settings.

Collectives™ on Stack Overflow

Match Multiple Lookup Array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related