Match & Index Help

hailey123

New Member
Joined
May 28, 2018
Messages
14
Hi - after some help please, I'm certain this has to be possible I just cannot figure it out for myself.

I have a whole lot of data that comes through all in 1 column. I am needing to match 2 cells within that column, and if those two cells are met - return a value that will be located underneath.

Example:

A
1​
Green
2​
Apple
3​
Fruit
4​
$1​

If that is my data in column A, and then in B1 I have "Green" and C1 I have "Apple", I need to return the value of $1 in A4. I have worked out I can use =INDEX(A:A,MATCH(B1,A:A,0)+3,1) will match green and locate the cell that contains the price, but i need to match both A1 and A2 and then find A4
Is this at all possible?
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,872
Office Version
  1. 365
Platform
  1. Windows
Post more of your data so that we can see 20 values in column A
Provide 2 different examples of the matches that you are trying to make
Does column B contain anything?

thanks
 

hailey123

New Member
Joined
May 28, 2018
Messages
14
Nothing in column B at all. In the below I would need to match Blue + Apple to return whatever is 2 under that (in this case $1 in A4), Blue + Banana and return whatever is 2 under that ($3

A
1​
Blue
2​
Apple
3​
Fruit
4​
$1.00​
5​
See Details
6​
Red
7​
Apple
8​
Fruit
9​
$2.00​
10​
See Details
11​
Blue
12​
Banana
13​
Fruit
14​
$3.00​
15​
See Details
16​
Red
17​
Banana
18​
Fruit
19​
$4.00​
20​
Organe
21​
Banana
21​
Fruit
21​
$5​
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,872
Office Version
  1. 365
Platform
  1. Windows
Try this
formula in ColumnB starting in cell B4 copied down
=IF(ISNUMBER(A4),A1&A2,"")

formula in E4
=INDEX(A:A,MATCH($E$1&$E$2,B:B,0))

Book1
ABCDE
1BlueColourred
2AppleObjectbanana
3Fruit
4$1.00BlueAppleResult$4.00
5See Details 
6Red 
7Apple 
8Fruit 
9$2.00RedApple
10See Details 
11Blue 
12Banana 
13Fruit 
14$3.00BlueBanana
15See Details 
16Red 
17Banana 
18Fruit 
19$4.00RedBanana
20Organe 
21Banana 
22Fruit 
23$5.00OrganeBanana
24
Sheet6
Cell Formulas
RangeFormula
E4E4=INDEX(A:A,MATCH($E$1&$E$2,B:B,0))
B4:B23B4=IF(ISNUMBER(A4),A1&A2,"")
 

Watch MrExcel Video

Forum statistics

Threads
1,118,986
Messages
5,575,383
Members
412,658
Latest member
LS0009
Top