Index & Match With (Exact Match & Approximate Match) Together

[John W]

Hi,

I have the following queries with 1 Reference Table (B2:G8) on the left and a Result Table (I2:J7) on the right.

I am trying to use the index and match function which involves exact match and approximate match together.

And based on the multiple criteria under J3:J5, I tried to get the outcome for J7=C.

I had tried using this function => INDEX(B3:B8,MATCH(AND((J3,B3:B8,0),MATCH(J4,D3:D8,1),MATCH(J5,F3:F8,1))))

But I keep having error message.

Can anyone help me on this?

Thank you.

 

[vletm]

John W​

Excel Resources is for Share URLs of Websites where you can find Excel, Access etc. stuff
>> moved this to Ask an Excel Question
A sample Excel-file is much better than any snapshot.

 

[bosco_yip]

Tried using this formula instead.

In J7

=INDEX((G3:G5,G6:G8),MATCH(J3,B3:B8,0),,MATCH(J4,D5:D6))

 

[John W]

John W​

Excel Resources is for Share URLs of Websites where you can find Excel, Access etc. stuff
>> moved this to Ask an Excel Question
A sample Excel-file is much better than any snapshot.

Hi vletm,

I am new in this blog so please advice me is it that I need to move this post to "Ask an Excel Question" forum, if so how do I do it?

I apologize if I have made this mistake.

Meanwhile I also attached my excel file for your advice.

Thank you.

 

[John W]

Tried using this formula instead.

In J7

=INDEX((G3:G5,G6:G8),MATCH(J3,B3:B8,0),,MATCH(J4,D5:D6))

Thanks bosco_yip.

But I need to match 3 criteria instead of 2.

Attached file for your reference.

Thank you.

 

[vletm]

John W​

# Your: I am new in this blog so please advice me is it that I need to move this post to "Ask an Excel Question" forum, if so how do I do it?

You've joined here few Years ago - new. Everyone can reread the Forum Rules by following link on the front page:

New Users - Please Start Here​

• Please post, new posts in the correct forums, not as Emails/Messages to people

# Please recheck Yourself - in which Forum is Your thread now?
As I wrote >> moved this to ... its has moved already.

# A sample file - it's much smoother to members who try to help You to see from a sample file - what is going on?

 

[bosco_yip]

Thanks bosco_yip.

But I need to match 3 criteria instead of 2.

Attached file for your reference.

Thank you.

Then,

What is your expected result in J7 ? , if the Age in J4 changed to 67.

 

[John W]

John W​

# Your: I am new in this blog so please advice me is it that I need to move this post to "Ask an Excel Question" forum, if so how do I do it?

You've joined here few Years ago - new. Everyone can reread the Forum Rules by following link on the front page:

New Users - Please Start Here​

• Please post, new posts in the correct forums, not as Emails/Messages to people

# Please recheck Yourself - in which Forum is Your thread now?
As I wrote >> moved this to ... its has moved already.

# A sample file - it's much smoother to members who try to help You to see from a sample file - what is going on?

Noted. Thank you.

 

[John W]

Then,

What is your expected result in J7 ? , if the Age in J4 changed to 67.

View attachment 84587

My mistake. Apologize for the error.

Attached is the revised copy with the designated outcome based on 4 scenarios.

Please advice.

Thank you.