VLOOKUP with multiple critiera

[Memar]

[FONT=&quot]I want to use VLookup or another formula to get a desired data in red. I couln't get the result that I need using this formula=VLOOKUP(val1&val2,data,col_index,0)
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]
[TABLE="class: Table TableWordWrap SCXW170678635, width: 0"]
<tbody class="SCXW170678635" style="margin: 0px; padding: 0px; user-select: text; -webkit-user-drag: none; -webkit-tap-highlight-color: transparent;">[TR="class: TableRow TableRowSelected"]
[TD="bgcolor: transparent"]Column A


[/TD]
[TD="bgcolor: transparent"]Column B


[/TD]
[TD="bgcolor: transparent"]Column C


[/TD]
[TD="bgcolor: transparent"]Column D


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[/TR]
[TR="class: TableRow TableRowSelected"]
[TD="bgcolor: transparent"]


[/TD]
[TD="bgcolor: transparent"]Product Item NO


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[/TR]
[TR="class: TableRow TableRowSelected"]
[TD="bgcolor: transparent"]


[/TD]
[TD="bgcolor: transparent"]D24162


[/TD]
[TD="bgcolor: transparent"]D34162


[/TD]
[TD="bgcolor: transparent"]D44162


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[/TR]
[TR="class: TableRow TableRowSelected"]
[TD="bgcolor: transparent"]JAN


[/TD]
[TD="bgcolor: transparent"]Cell B3 DESIRED RESULT 15


[/TD]
[TD="bgcolor: transparent"]10


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[/TR]
[TR="class: TableRow TableRowSelected"]
[TD="bgcolor: transparent"]FEB


[/TD]
[TD="bgcolor: transparent"]Cell B4 DESIRED RESULT 20


[/TD]
[TD="bgcolor: transparent"]20


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[/TR]
[TR="class: TableRow TableRowSelected"]
[TD="bgcolor: transparent"]MAR


[/TD]
[TD="bgcolor: transparent"]Cell B5 DESIRED RESULT 50


[/TD]
[TD="bgcolor: transparent"]5


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[/TR]
</tbody>[/TABLE]

[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]
[TABLE="class: Table TableWordWrap SCXW170678635, width: 0"]
<tbody class="SCXW170678635" style="margin: 0px; padding: 0px; user-select: text; -webkit-user-drag: none; -webkit-tap-highlight-color: transparent;">[TR="class: TableRow TableRowSelected"]
[TD="bgcolor: transparent"]


[/TD]
[TD="bgcolor: transparent"]D24162




[/TD]
[TD="bgcolor: transparent"]D34162




[/TD]
[TD="bgcolor: transparent"]D44162




[/TD]
[/TR]
[TR="class: TableRow TableRowSelected"]
[TD="bgcolor: transparent"]JAN


[/TD]
[TD="bgcolor: transparent"]15


[/TD]
[TD="bgcolor: transparent"]10


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[/TR]
[TR="class: TableRow TableRowSelected"]
[TD="bgcolor: transparent"]FEB


[/TD]
[TD="bgcolor: transparent"]20


[/TD]
[TD="bgcolor: transparent"]20


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[/TR]
[TR="class: TableRow TableRowSelected"]
[TD="bgcolor: transparent"]MAR


[/TD]
[TD="bgcolor: transparent"]50


[/TD]
[TD="bgcolor: transparent"]5


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[/TR]
[TR="class: TableRow TableRowSelected"]
[TD="bgcolor: transparent"]APR


[/TD]
[TD="bgcolor: transparent"]30


[/TD]
[TD="bgcolor: transparent"]6


[/TD]
[TD="bgcolor: transparent"]


[/TD]
[/TR]
</tbody>[/TABLE]

[/FONT]
[FONT=&quot]
[/FONT]
Thank you for your help and time

 

[Ron Coderre]

using this example...
This structure is in A1:D5
[TABLE="width: 320"]
<tbody>[TR]
[TD][/TD]
[TD]Product Item NO[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]D24162[/TD]
[TD]D34162[/TD]
[TD]D44162[/TD]
[/TR]
[TR]
[TD]JAN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FEB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MAR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
and this data set in A7:D11
[TABLE="width: 320"]
<tbody>[TR]
[TD][/TD]
[TD]D24162[/TD]
[TD]D34162[/TD]
[TD]D44162[/TD]
[/TR]
[TR]
[TD]JAN[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FEB[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MAR[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]APR[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This regular formula, copied across and down, returns the monthly totals for the matching ref nums:

B3: =SUMPRODUCT(($A$8:$A$11=$A3)*($B$7:$D$7=B$2)*$B$8:$D$11)

Is that something you can work with?

 

[Memar]

Thank you for the quick response. it gives me value error. The number in Cell B8 and D11 is a result from SUMIF formula.

 

[Aladin Akyurek]

Using VLOOKUP, we would get...

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

Product​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]D24162[/TD]
[TD]D34162[/TD]
[TD]D44162[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD]D24162[/TD]
[TD]D34162[/TD]
[TD]D44162[/TD]
[TD][/TD]
[TD]JAN[/TD]
[TD]

15​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]JAN[/TD]
[TD]

15​

[/TD]
[TD]

10​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]FEB[/TD]
[TD]

20​

[/TD]
[TD]

20​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]FEB[/TD]
[TD]

20​

[/TD]
[TD]

20​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]MAR[/TD]
[TD]

50​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]MAR[/TD]
[TD]

50​

[/TD]
[TD]

5​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]APR[/TD]
[TD]

30​

[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


B3, copied across to D3 and down:

=VLOOKUP($A3,$F:$I,MATCH(B$2,INDEX($F:$I,1,0),0),0)