Combine IF with Vlookup

[akswartz85]

I have one table with unique identifiers and different years. I want to pull amounts from additional tabs into the cells only labeled with the $ below:

Tab name: MERGED

Unique ID	2015	2016	2017
1234A	$	$	$
4567B	$	$	$
9038B	$	$	$
9038J	$	$	$

<tbody>
</tbody>

Additional tabs (each ID may be listed 1+ times):
Tab name: ALL AMOUNTS

Unique ID	YEAR	Amount
1234A	2015	342
1234A	2016	394
4567B	2017	390
4567B	2015	908
9038J	2017	908
9038J	2016	3221
9038B	2017	9033

<tbody>
</tbody>

I'd like to pull the amount into the proper year column by ID. Ideally, it would look like:

Unique ID	2015	2016	2017
1234A	$342	$394	No amount
4567B	$908	No amount	$390
9038B	No amount	No amount	$9033
9038J	No amount	$3221	$908

<tbody>
</tbody>

I was thinking I could write an IF(Vlookup)
=if('all amounts'$b2="2015",vlookup('merged'$a2,'all amounts'$a:$c,3,false),"No amount")

(forgive me if I forgot any formatting, I'm making this dataset up bc my actual data has identifying info)

But when I drag the formula down, I just get "no amount" (i.e. the if failed).

Thoughts? My head is spinning.

 

[Legacy 143009]

{=IFNA(INDEX('MERGED'!$C:C,MATCH('ALL AMOUNTS'!$A2&'ALL AMOUNTS'!B$1,'MERGED'!$A:$A&'MERGED'!$B:$B,0)),"No Amount")}

It's an array formula so you have to Ctrl+Shift+Enter after paste.

Have a nice day

 

[akswartz85]

Thanks!

Why 'MERGED'!$C:C?

 

[Legacy 143009]

Thanks!

Why 'MERGED'!$C:C?

Ahh sorry All the names will be vice versa

ALL AMOUNTS => MERGED
MERGED => ALL AMOUNTS

 

[Legacy 143009]

Paste this to MERGED B2 and continue:

{=IFNA(INDEX('ALL AMOUNTS'!$C:C,MATCH($A2&B$1,'ALL AMOUNTS'!$A:$A&'ALL AMOUNTS'!$B:$B,0)),"No Amount")}

 

[Aladin Akyurek]

Without concatenation...

ALL AMOUNTS (data in A:C)

Unique ID	YEAR	Amount
1234A	2015	342
1234A	2016	394
4567B	2017	390
4567B	2015	908
9038J	2017	908
9038J	2016	3221
9038B	2017	9033

<tbody>
</tbody>

merged (processing in A:D)

Unique ID	2015	2016	2017
1234A	342	394	no amount
4567B	908	no amount	390
9038B	no amount	no amount	9033
9038J	no amount	3221	908

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

In B2 control+shift+enter, not just enter, copy across, and down:

=IFNA(INDEX('ALL AMOUNTS'!$C$2:$C$8,MATCH($A2,IF('ALL AMOUNTS'!$B$2:$B$8=B$1,'ALL AMOUNTS'!$A$2:$A$8),0)),"no amount")

If you get a #NAME ? error, replace IFNA with IFERROR.

 

[Legacy 143009]

IF('ALL AMOUNTS'!$B$2:$B$8=B$1,'ALL AMOUNTS'!$A$2:$A$8)

Aaahh... I didn't know that you can return an array from IF function. Nice to learn new things everyday!

 

[Aladin Akyurek]

Aaahh... I didn't know that you can return an array from IF function. Nice to learn new things everyday!

IF is a filter function and as such it's bread and butter of array-processing formulas.

 

[akswartz85]

Thanks all --- back at this project tonight -- will put everything into the sheet shortly. Appreciate the help.

 

[akswartz85]

Without concatenation...

ALL AMOUNTS (data in A:C)

Unique ID	YEAR	Amount
1234A	2015	342
1234A	2016	394
4567B	2017	390
4567B	2015	908
9038J	2017	908
9038J	2016	3221
9038B	2017	9033

<tbody>
</tbody>

merged (processing in A:D)

Unique ID	2015	2016	2017
1234A	342	394	no amount
4567B	908	no amount	390
9038B	no amount	no amount	9033
9038J	no amount	3221	908

<tbody>
</tbody>

In B2 control+shift+enter, not just enter, copy across, and down:

=IFNA(INDEX('ALL AMOUNTS'!$C$2:$C$8,MATCH($A2,IF('ALL AMOUNTS'!$B$2:$B$8=B$1,'ALL AMOUNTS'!$A$2:$A$8),0)),"no amount")

If you get a #NAME ? error, replace IFNA with IFERROR.

So, I shifted your formula ot my actual data set, and it's not working.

I have:
=IFNA(INDEX(map_fall_math_only!$O:$O,MATCH($A2,IF(map_fall_math_only!$B:$B="2015",map_fall_math_only!$A:$A),0)),"No test result")

Where map_fall_math_only = all amounts
map_fall_math_only!O:O = the col I'm trying to pull into merged
"" $b:$b = year that I want to filter on
"" $a:$a = unique identifier across both sheets

I am just getting "no test result" on everything. I am clicking ctl+ shift + enter.

Thoughts?