Combining VLOOKUP and INDEX/MATCH?

[sheller]

I know this is easier than I'm making it, but I can't figure out how to to a vlookup with multiple criteria, or maybe using index match?

On worksheet 1(let's say colum A). I have Item numbers, then a column for preship score and a column for arrival score.
On worksheet 2 in column A i have item numbers, in column b if have the sample type (Arrival or Preshipment), and in column 3 I have the score.

Essentially I need to figure out how, in column B of worksheet 1, I can pull in all Preship scores IF the Item numbers match b/t sheets 1 and 2, and the sample type is "Preshipment"
LIkewise, I need to figure out how, in column C of worksheet 1, I can pull in all arrival scores IF the Item number match b/t sheets 1 and 2, and the Sample type is "Arrival"

Thanks very much in advance

Worksheet 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Item Number
[/TD]
[TD]Preship Score
[/TD]
[TD]Arrival Score
[/TD]
[/TR]
[TR]
[TD]P-1234-B
[/TD]
[TD](need formula)
[/TD]
[TD](need formula)
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Worksheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Item Number
[/TD]
[TD]Sample Type
[/TD]
[TD]Score
[/TD]
[/TR]
[TR]
[TD]P-1234-B
[/TD]
[TD]Preship
[/TD]
[TD]23
[/TD]
[/TR]
[TR]
[TD]P-1234-B
[/TD]
[TD]Arrival
[/TD]
[TD]25
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Martin2404]

You could trying something like this (I have assumed B2 and C2 are the 'need formula' cells in sheet1, and the sheets are named 'Sheet1' and 'Sheet2')

You need to force the formulas by entering them with Left Ctrl+Left Shift+Enter

Under Preship Score
=INDEX(Sheet2!C:C,MATCH(A2&"Preship",Sheet2!A:A&Sheet2!B:B,0))

Under Arrival Score
=INDEX(Sheet2!C:C,MATCH(A2&"Arrival",Sheet2!A:A&Sheet2!B:B,0))

 

[sheller]

Hi Martin,
For some reason, it didn't seem to work. You're correct, B2 and C2 aare the 'need formula' cells in sheet1. I did ctrl+shift+enter and it did array, but got the "#N/A" value. To confirm, in sheet one, I don't have any "arrival" or "preship" words (aside from column headers).

 

[Martin2404]

Did you hold down Left Ctrl+Left Shift while pressing Enter key when entering the formula ?

Also check the name on you spreadsheet tab, is it named 'Sheet2' or 'Worksheet2" ?


I've just tried it again and it worked.

Tab name Sheet1

[TABLE="width: 294"]
<tbody>[TR]
[TD="class: xl62, width: 90"]Item Number[/TD]
[TD="class: xl62, width: 102"]Preship Score[/TD]
[TD="class: xl62, width: 102"]Arrival Score[/TD]
[/TR]
[TR]
[TD="class: xl62, width: 90"]P-1234-B[/TD]
[TD="class: xl62, width: 102, align: right"]23[/TD]
[TD="class: xl62, width: 102, align: right"]25[/TD]
[/TR]
</tbody>[/TABLE]


Tab Name Sheet2
[TABLE="width: 294"]
<tbody>[TR]
[TD="class: xl62, width: 90"]Item Number[/TD]
[TD="class: xl62, width: 102"]Sample Type[/TD]
[TD="class: xl62, width: 102"]Score[/TD]
[/TR]
[TR]
[TD="class: xl62, width: 90"]P-1234-B[/TD]
[TD="class: xl62, width: 102"]Preship[/TD]
[TD="class: xl62, width: 102, align: right"]23[/TD]
[/TR]
[TR]
[TD="class: xl62, width: 90"]P-1234-B[/TD]
[TD="class: xl62, width: 102"]Arrival[/TD]
[TD="class: xl62, width: 102, align: right"]25[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 294"]
<tbody>[TR]
[/TR]
[TR]
[TD="class: xl62, width: 102, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[sheller]

Hi Martin,

It worked! Thanks for the reminder. I think i had an issue with named ranges, but solved it, and did cntl+shift+enter, and did the trick. Thank you!

Did you hold down Left Ctrl+Left Shift while pressing Enter key when entering the formula ?

Also check the name on you spreadsheet tab, is it named 'Sheet2' or 'Worksheet2" ?


I've just tried it again and it worked.

Tab name Sheet1

[TABLE="width: 294"]
<tbody>[TR]
[TD="class: xl62, width: 90"]Item Number[/TD]
[TD="class: xl62, width: 102"]Preship Score[/TD]
[TD="class: xl62, width: 102"]Arrival Score[/TD]
[/TR]
[TR]
[TD="class: xl62, width: 90"]P-1234-B[/TD]
[TD="class: xl62, width: 102, align: right"]23[/TD]
[TD="class: xl62, width: 102, align: right"]25[/TD]
[/TR]
</tbody>[/TABLE]


Tab Name Sheet2
[TABLE="width: 294"]
<tbody>[TR]
[TD="class: xl62, width: 90"]Item Number[/TD]
[TD="class: xl62, width: 102"]Sample Type[/TD]
[TD="class: xl62, width: 102"]Score[/TD]
[/TR]
[TR]
[TD="class: xl62, width: 90"]P-1234-B[/TD]
[TD="class: xl62, width: 102"]Preship[/TD]
[TD="class: xl62, width: 102, align: right"]23[/TD]
[/TR]
[TR]
[TD="class: xl62, width: 90"]P-1234-B[/TD]
[TD="class: xl62, width: 102"]Arrival[/TD]
[TD="class: xl62, width: 102, align: right"]25[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 294"]
<tbody>[TR]
[/TR]
[TR]
[TD="class: xl62, width: 102, align: right"][/TD]
[/TR]
</tbody>[/TABLE]