Matching cells from a column and returning value from a third column.

[NicolaiK]

Hello all,

I am having difficulties with matching information from two columns and returning information from a third column.

Example:

Column A has 50 rows each with a value
Column B has 30 Rows each with a corresponding value
Column C has 30 Rows each with a value

I need a formula that match A and B and when they match, return me the value from C

I am currently trying to make this IF formula work: =IF(A1=B1:B30,C1:C30,0)

Anyone have a solution for me?

Thanks for helping out!

 

[FDibbins]

See if what I posted here will help you?
https://www.mrexcel.com/forum/excel-questions/1029793-vlookupifs-vlookup-multiple-conditions.html

 

[NicolaiK]

Thank you for trying to help FDibbins! But I cant get the solution you sent to work for me... could you try to explain the J2=INDEX(A$2:A$6,MATCH($H$1&$H$2&$H$3,INDEX($A$2:$A$6&$D$2:$D$6&$B$2:$B$6,0),0)) formula using my problem?

Is this correct?

=INDEX(A1:A50,MATCH(A1,INDEX(B1:B30),0)0)

I dont think I understand what the different parts of the formula does, and so the formula might work if I input the data correctly?

 

[FDibbins]

You have 2 criteria you want to match - A and B, and if they match, you want to return C. Where do you want the results shown, and where will the criteria be that you want to match against?

 

[NicolaiK]

Yes thats correct.
The result can be shown in D1.

The criteria is A1, so if B1=A1 return C1 in D1

 

[NicolaiK]

correction: B1 meaning any of the values between B1:B30 matching A1

 

[FDibbins]

So then maybe something like this?
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]

10​

[/td][td]

10​

[/td][td]a[/td][td]a[/td][/tr]

[tr][td]

2​

[/td][td]

20​

[/td][td]

12​

[/td][td]b[/td][td][/td][/tr]

[tr][td]

3​

[/td][td]

30​

[/td][td]

14​

[/td][td]c[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

40​

[/td][td]

16​

[/td][td]d[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

50​

[/td][td]

18​

[/td][td]e[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

60​

[/td][td]

20​

[/td][td]f[/td][td]b[/td][/tr]

[tr][td]

7​

[/td][td]

70​

[/td][td]

22​

[/td][td]g[/td][td][/td][/tr]

[tr][td]

8​

[/td][td]

80​

[/td][td]

24​

[/td][td]h[/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

90​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]

100​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]
D1=IFERROR(INDEX($C$1:$C$8,MATCH(B1,$A$1:$A$10,0)),"")
copied down

 

[NicolaiK]

This worked Perfectly as far as I can see right now! Thank you so much!!

 

[FDibbins]

Happy to help