Get the data by looking up 2 values simultaneously similar to Vlookup

[raju.r.083]

Hi all,
Greetings!!!

Can anyone please help me out in getting a solution for the below problem.

I have created 2 sheets in the attached workbook for my explanation. One is "Master sheet" and the other one is "To be filled".

My requirement is that,
I need to get the data extracted from "Master sheet" in the sheet "To be filled" for the columns that are empty ('Name' column & 'Material' column).

The data has to be found out by looking at 2 values simultaneously (i.e. using 'Title' and 'Rev').

Like using Vlookup, we give only 1 Lookup value and get the required data by specifying certain range.

Can anyone please help me out in getting a formula (Not Macro) on "HOW TO GET THE VALUES / FILL THE CELLS BY LOOKING UP 2 VALUES SIMULTANEOUSLY" for the column 'Name' & 'Material' in the sheet "To be filled"?

Many Thanks in advance for your time and to the solution that was provided.

[AliGW]

Here's one way. In D2:

=INDEX('Master sheet'!$D$2:$D$76,MATCH(1,('Master sheet'!$B$2:$B$76='To be filled'!B2)*('Master sheet'!$C$2:$C$76='To be filled'!C2),0))

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Once confrimed, drag copy down.

[MarvinP]

Hi raju,

Here is a non CSE formula that will do your work.

In D2 put this formula:

=LOOKUP(2,1/(('Master sheet'!$B$1:$B$76='To be filled'!$B2)*('Master sheet'!$C$1:$C$76='To be filled'!$C2)),'Master sheet'!$D$1:$D$76)

See attached for similar other column formula.

Double Lookup for raju.xlsx

[raju.r.083]

Hi AliGW, Marvin,

Thank you for your quick response.
Your solutions are working excellent!!! Its working as per my requirement.
Thanks to your solution and to your time.

Thanks a lot!!!!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.