vlookup using 2 condition one based on devices and grouping column

[zackdy]

Hi All,
I would like to request for help on this excel file. Attach below are 2 sample file and inside it consist of device,grouping and lb1,tb1,ab1. How do I use excel formulae based on 2 condition and set from vlookup with 2 condition file to vlookup with 2 condition 1 file like a vlookup. Pls support me on this.

[oeldere]

It's a very poor example (at least to me).

Why not use 1 file for all data an calclations.

I suppose you need to look at index, match.

[zackdy]

yea but how do you apply that formulae which is something that i dont get it and sorry for the poor sample

[zackdy]

Sorry about that,
Hope my sample is more understandable and easy to read. I would like my data in sheet2 get from sheet1 and input the answer based on 2 condition one is grouping name and device.

[oeldere]

I used a helcolum (see yellow cells) to determine the unique value.

After that I used index/match

Since i post an excel 2003 file you find errors in the file.

in excel 2007 you can solve that problem using =iferror(myforumula,"")

See the attached file.

[zackdy]

So meaning i have to depend on the extra stuff you create to determine the value?

[oeldere]

Yes, It is the easiest way (at least for me).

I can be on another worksheet (that's not a problem for the formula)

I made the formula on 1 sheet so it is easier to understand.

[zackdy]

can i use & in between the match so that it can refer to 2 cell to determine the output correct or not?

[oeldere]

You can give it a try and post the result on this forum.

In that case other forummembers can also find this result (if they use the search option on this forum).

[zackdy]

I put it this way but no answer as i knew something was wrong.

=INDEX($D$4:$J$8,MATCH($D18,$D$4:$D$8,0)&MATCH($E18,E5:E8,0),MATCH(F$17,$D$4:$J$4,0
look on the one with #ref i thought can but thats not the right way

[oeldere]

Maybe it is possible, but what is the problem with an helpcolumn (as I already showed you)?

In that case you get the desired result.

[zackdy]

Maybe it is possible, but what is the problem with an helpcolumn (as I already showed you)?

In that case you get the desired result.

It works too i thank you to you for the help but of course i also need futher understand on it as i dont open my forum regularly