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.

 

[akswartz85]

Hmm, it appears it has something to do with me changing this: map_fall_math_only!$B:$B="2015"

to "2015" instead of a cell reference. Do you know why?

 

[snjpverma]

Another solution you can try

Just enter,

=IFERROR(VLOOKUP(A2&2015,IF({1,0},INDEX(map_fall_math_only!A1:A2000&map_fall_math_only!B1:B2000,0),map_fall_math_only!O1:O2000),2,0),"No test result")

you can extend or shorten the reference highlighted in red as per your requirement.

 

[snjpverma]

Hmm, it appears it has something to do with me changing this: map_fall_math_only!$B:$B="2015"

to "2015" instead of a cell reference. Do you know why?

it might be in a different format or with a space at the end or beginning of 2015 in your reference cell. re-type it manually and change the format to number and try.

 

[Aladin Akyurek]

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?

Try to unquote 2015...

{=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")}
​