Multiple criteria match to return adjacent cell value

Hello everyone,

I've been banging my head against the wall for days trying to figure out the best way to accomplish this task.

I have two sets of data that partially contain information that I need to query to return data from one of the sheets.

Example:
- Sheet 1 lists computers by make/model/designation, each in a unique, separate column. Some have data in all 3 columns, some only in 2.
- Sheet 2 lists support options for computers based on make/model/designation, again, each in a unique, separate column, and with Support in a 4th column.

Objective:
I am trying to run a query against a large set of data that matches Make column, Model column, Designation columns, then returns the Support column.

So far, I've been mostly dead in the water. I'm not sure if I need to be doing an INDEX and a MATCH, or an IF with a nested MATCH... and I have no idea how do have it return the Support cell value based on the other criteria being met.

Sheet 1 Might look like:

Make | Model | Designation
Dell | 5600 | P4
HP | 9000 | P3
Dell | 5600 | P2
Acer | 300 | P4
(continue for 2000 rows)

Sheet 2 might look like:

Make | Model | Designation | Supported
Dell | 5600 | P4 | Yes
Dell | 5600 | P2 | No
HP | 9000 | P3 | Yes
HP | 8000 | P2 | No
Acer | 300 | P4 | Yes

This support matrix is about 48 rows, and contains all support yes/no for all models. I am trying to apply it to the 2000 rows of assets listed in inventory.

The result that I'm looking for is that when there is an exact match of Make/Model/Designation on both sheets, that the support yes/no is returned in Sheet 1.

Any help would be greatly appreciated, and may save me from a heart attack.

Thank you!

Originally Posted by JohnTopley

Try

on Sheet1 D2

=IFERROR(INDEX(Sheet2!$D$2:$D$50,MATCH(1,(Sheet2!A2:A50=$A2)*(Sheet2!B2:B50=$B2)*(Sheet2!C2:C50=$C2),0)),"")

Enter with Ctrl+shift+EnterCopy down

Thank you for the help! I'm getting close.

I took the string you provided and tested it in a simple spreadsheets with the example I gave above. With some tweaks, I was able to get it returning as hoped.

However... When I tried to apply it to the data that I am hoping to use it with, I am getting a small error.

Here is the modified string... =IFERROR(INDEX('Supp YN'!$E$2:$E$48,MATCH(1,('Supp YN'!$A$1:$A$48=L13)*('Supp YN'!$B$1:$B$48=M13)*('Supp YN'!$C$1:$C$48=N13),0)),"No Match")

It is returning data, but is one cell off. For example, if the match function finds all the criteria met on SuppYN!A5, B5, C5 it is returning a value of E6 instead of the desired E5.

I've been looking, but am not seeing what is the issue.

Suggestions?

Thank you,
Adam

=IFERROR(INDEX('Supp YN'!$E$2:$E$48,MATCH(1,('Supp YN'!$A$2:$A$48=L13)*('Supp YN'!$B$2:$B$48=M13)*('Supp YN'!$C$2:$C$48=N13),0)),"No Match")

Change the range is indicated by the red .

The range is the search range for the match function, though. Correct?

What I attempted to do was set the range set with static values to query a reference table of make/model/designation/support in Sheet 2.

Sheet1 looks for a match in that table based on multiple criteria and returns the support value associated.

Right now it is doing the match, but it is returning a value that is one row down.

Originally Posted by Adam859

Hello everyone,

I've been banging my head against the wall for days trying to figure out the best way to accomplish this task.

I have two sets of data that partially contain information that I need to query to return data from one of the sheets.

Example:
- Sheet 1 lists computers by make/model/designation, each in a unique, separate column. Some have data in all 3 columns, some only in 2.
- Sheet 2 lists support options for computers based on make/model/designation, again, each in a unique, separate column, and with Support in a 4th column.

Objective:
I am trying to run a query against a large set of data that matches Make column, Model column, Designation columns, then returns the Support column.

So far, I've been mostly dead in the water. I'm not sure if I need to be doing an INDEX and a MATCH, or an IF with a nested MATCH... and I have no idea how do have it return the Support cell value based on the other criteria being met.

Sheet 1 Might look like:

Make | Model | Designation
Dell | 5600 | P4
HP | 9000 | P3
Dell | 5600 | P2
Acer | 300 | P4
(continue for 2000 rows)

Sheet 2 might look like:

Make | Model | Designation | Supported
Dell | 5600 | P4 | Yes
Dell | 5600 | P2 | No
HP | 9000 | P3 | Yes
HP | 8000 | P2 | No
Acer | 300 | P4 | Yes

This support matrix is about 48 rows, and contains all support yes/no for all models. I am trying to apply it to the 2000 rows of assets listed in inventory.

The result that I'm looking for is that when there is an exact match of Make/Model/Designation on both sheets, that the support yes/no is returned in Sheet 1.

Any help would be greatly appreciated, and may save me from a heart attack.

Thank you!

As in the example above, the lookup table is on Supp YN, which in that example is Sheet 2.

Sheet 1 is a worksheet that is 2000 lines long, and I am matching criteria from columns L, M, N against columns A, B, C in Sheet 2, and if they match, returning a value into Sheet 1 column O from Sheet 2 column E.

My understanding is that the MATCH function looks at L2, M2, N2 in Sheet 1, then compares it to a table that is being indexed in Sheet 2.

Am I explaining poorly? If so, I apologize. I am learning as I go

I'm sorry. I'm missing the whatever may be causing the functions to return data that is consistently one cell off.

Thank you! I was able to get it working with help from everyone here.