Extract Values from Dependent Lists based on Twin Criteria

[Whitenoise]

I am looking at a way to extract values based on twin criteria, preferrably using vlookup or any other method.
I have 2 constraints -
One, I can't have any VBA code in my spreadsheet
Two, I may not be able to add any additional columns in my sheet 1

Requirement: I would like to get values from Col-H in Sheet 1 into Col-F in Sheet 2

I have tried using the below method to obtain values in Col F (cell F2) in Sheet2, but unable to get the results in any scenario.

=IFERROR(VLOOKUP(Sheet2!D2&E2,Sheet1!$A$2:$H$16,8,FALSE),"")

The screenshots below give a preview of sheet 1 and sheet 2 of sample data

https://drive.google.com/drive/folders/1SVchlsEV6K27CA1D0cIw8IcFPAnbi-nb?usp=sharing

 

[Fluff]

Hi & welcome to MrExcel.
How about
=INDEX(Sheet1!$H$2:$H$4,MATCH(D2&E2,Sheet1!$A$2:$A$4&Sheet1!$E$2:$E$4,0))

This is an array formula & needs to be confirmed with Ctrl Shift Enter, rather than just Enter

 

[PCL]

Do I am wrong to say that you want in sheet2 data from Sheet1 using value from Sheet2 columns D & E and seraching in sheet1 columns A & E and returning values from Sheet1 column H
It seems there is merged cells is it ?

 

[Whitenoise]

The problem with the Index-Match is that the list needs to be sorted. However, in my case, the dependents lists in sheet 1 are not sorted and they should not be sorted.

https://drive.google.com/open?id=1vj8xDoiIZZhZA2yWqUiPgnxW1k5T6b2I

 

[Fluff]

Index/Match does not need the data to be sorted, what you need to do is expand the ranges to match your data.

 

[Whitenoise]

Thanks a ton, it works.

One follow-up question, how can I remove "#N/A" from cell where {=INDEX(Sheet1!$H$2:$H$4,MATCH(D2&E2,Sheet1!$A$2:$A$4&Sheet1!$E$2:$E$4,0))} is mentioned. I would want to write

=IFERROR(INDEX(Sheet1!$H$2:$H$4,MATCH(D2&E2,Sheet1!$A$2:$A$4&Sheet1!$E$2:$E$4,0)),"") but unable to use Ctrl+Shift+Enter

 

[Whitenoise]

Do I am wrong to say that you want in sheet2 data from Sheet1 using value from Sheet2 columns D & E and seraching in sheet1 columns A & E and returning values from Sheet1 column H
It seems there is merged cells is it ?

You are not wrong in trying to understand the requirement. And there are merged cells.

 

[Fluff]

This is correct
=IFERROR(INDEX(Sheet1!$H$2:$H$4,MATCH(D2&E2,Sheet1!$A$2:$A$4&Sheet1!$E$2:$E$4,0)),"")
and you should still be able to confirm it with Ctrl Shift Enter.

 

[Whitenoise]

Problem resolved. Really thanks Fluff

This is correct
=IFERROR(INDEX(Sheet1!$H$2:$H$4,MATCH(D2&E2,Sheet1!$A$2:$A$4&Sheet1!$E$2:$E$4,0)),"")
and you should still be able to confirm it with Ctrl Shift Enter.

 

[Fluff]

Glad you sorted it & thanks for the feedback