Match & Index Help

[hailey123]

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?

 

[Yongle]

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]

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]

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
	A	B	C	D	E
1	Blue			Colour	red
2	Apple			Object	banana
3	Fruit
4	$1.00	BlueApple		Result	$4.00
5	See Details
6	Red
7	Apple
8	Fruit
9	$2.00	RedApple
10	See Details
11	Blue
12	Banana
13	Fruit
14	$3.00	BlueBanana
15	See Details
16	Red
17	Banana
18	Fruit
19	$4.00	RedBanana
20	Organe
21	Banana
22	Fruit
23	$5.00	OrganeBanana
24
Sheet6

Cell Formulas
Range	Formula
E4	E4	=INDEX(A:A,MATCH($E$1&$E$2,B:B,0))
B4:B23	B4	=IF(ISNUMBER(A4),A1&A2,"")