LOOKUP with two criteria.

[MikaB]

Hi,

I have an issue and I am trying to find an elegant way to achieve something. I hope someone could advice me here.

So basically I have this sort of Excel sheet, which I would like to populate from another Excel sheet.

Values X Y Z
1
2
3


So the above table I'd like to fill in from another sheet. I have the control of that other sheet, so I can also rearrange the data if that is any help.
Right now, just so that I can explain what I want to achieve here, I have put the data in that other sheet to look like this:

Value Col Content
1 X something 1
2 X something 2
3 X something 3
1 Y something 4
2 Y something 5
3 Y something 6
1 Z something 7
2 Z something 8
3 Z something 9


So based on this, I would like to populate the first table like this:

Values X Y Z
1 something 1 something 4 something 7
2 something 2 something 5 something 8
3 something 3 something 6 something 9

So basically, I would like to look from table in another sheet a value based on Value and Col matches.

What would be the easiest way to achieve this? Some lookup function perhaps? And would it make things easier to format the data on another sheet?

Thanks in advance.

Br,
Mika

[torachan]

it would make more sense to upload your workbook - see big yellow banner - manually put your expected output into a sheet.

[Kyle123]

You want either, Index/Match formulas or Get & Transform

[Glenn Kennedy]

Hi. Forum Rule 1 is very short. It states: "Title must briefly summarize your request."

A GOOD thread title is the sort of thing you would use as a search term on Google*. Your chosen title would produce a squillion useless hits. In future, please take a bit more time to think of a descriptive title that would be "Google-friendly". Many people search the forum answers looking for help and your very general title wouldn't help them at all.

Since you are a new user of this site, on this occasion, I have changed it for you.

However, if you continue to use weak titles, you can expect to have your thread BLOCKED until you change it yourself. If that happens, to edit the thread title, open the original post, click "Edit Post" (bottom right) to access the area where you can edit your title.

*Other search engines are available!!!

Regards,

Glenn.

[Glenn Kennedy]

A sample file is ALWAYS preferred (yellow banner, top of page). However...

=INDEX(Sheet1!$C$2:$C$10,MATCH(1,INDEX((Sheet1!$A$2:$A$10=$A2)*(Sheet1!$B$2:$B$10=B$1),0),0))

copied across and dnown