Combine IF with Vlookup

akswartz85

New Member
Joined
Mar 31, 2010
Messages
49
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 ID201520162017
1234A$$$
4567B$$$
9038B$$$
9038J$$$

<tbody>
</tbody>

Additional tabs (each ID may be listed 1+ times):
Tab name: ALL AMOUNTS
Unique IDYEARAmount
1234A2015342
1234A2016394
4567B2017390
4567B2015908
9038J2017908
9038J20163221
9038B20179033

<tbody>
</tbody>

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

Unique ID201520162017
1234A$342$394No amount
4567B$908No amount$390
9038BNo amountNo amount$9033
9038JNo 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.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Flashbond

Well-known Member
Joined
Mar 13, 2010
Messages
670
{=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 ;)
 

Flashbond

Well-known Member
Joined
Mar 13, 2010
Messages
670

ADVERTISEMENT

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")}
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Without concatenation...

ALL AMOUNTS
(data in A:C)

Unique IDYEARAmount
1234A2015342
1234A2016394
4567B2017390
4567B2015908
9038J2017908
9038J20163221
9038B20179033

<tbody>
</tbody>

merged (processing in A:D)

Unique ID201520162017
1234A342394no amount
4567B908no amount390
9038Bno amountno amount9033
9038Jno amount3221908

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


In B2 control+shift+enter, not just enter, copy across, and down:
Rich (BB code):
=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.
 

Flashbond

Well-known Member
Joined
Mar 13, 2010
Messages
670

ADVERTISEMENT

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!
 

akswartz85

New Member
Joined
Mar 31, 2010
Messages
49
Thanks all --- back at this project tonight -- will put everything into the sheet shortly. Appreciate the help.
 

akswartz85

New Member
Joined
Mar 31, 2010
Messages
49
Without concatenation...

ALL AMOUNTS
(data in A:C)

Unique IDYEARAmount
1234A2015342
1234A2016394
4567B2017390
4567B2015908
9038J2017908
9038J20163221
9038B20179033

<tbody>
</tbody>

merged (processing in A:D)

Unique ID201520162017
1234A342394no amount
4567B908no amount390
9038Bno amountno amount9033
9038Jno amount3221908

<tbody>
</tbody>


In B2 control+shift+enter, not just enter, copy across, and down:
Rich (BB code):
=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?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,250
Messages
5,527,633
Members
409,778
Latest member
MagalieD

This Week's Hot Topics

Top