Match and return value

[bakermetal]

I am trying to write a formula on the “Applied Parts” tab in column D to where if there is a cell in column A on the Data tab that matches a cell in column C on the “applied Parts” tab, it will return the value in column B in the “Data” tab. So on the attached spreadsheet the green value for D4 would be “2”. Is there a way to do that?

[teylyn]

Hi bakermetal,

welcome to the forum and thanks for revising your thread title.

if I understand your requirements correctly, you could try this formula in the "Applied Parts" sheet in cell D2 and copy down

=VLOOKUP(C2,Data!$A$2:$B$315,2,FALSE)

hth

[DonkeyOte]

I think SUMIF would be better myself given we're returning numerics... (hence the suggested re-title)

=SUMIF(Data!$A:$A,$C2,Data!$B:$B)

this will return 0 for no matches whereas you would require an Error handler for the VLOOKUP should the codes not exist on data sheet...

Using SUMIF we're also making assumption of single match or if dupe matches return aggregation thereof.

[teylyn]

bakermetal,
when DonkeyOte is right, he's right! My formula will produce an error if the lookup value can not be found, and would require encasing in an IF() statement to manage that, whereas DonkeyOte's formula will gracefully present you with a zero for values not found in the list.

Check back if you need more help.

cheers

[bakermetal]

Thanks teylyn and DonkeyOte. They both work like a champ. I tried all day yesterday to figure this out. I will study both formulas. Excel is such an awesome tool.

Thanks again. Y’all are the best.

[bakermetal]

Okay, I’m trying to take it one step further. I think I’m close, but my formula is not working. Here is what I’m trying to get the formula to do for me in cell F2:

If value in D2 = 0 and C2 matches column A, then return value in E2

The formula I’m trying is:

=SUMIF(AND(D2=”0”),AND($A:$A,$C2,$E:$E))

What am I doing wrong?

Thanks for your help