VLOOKUP or INDEX/MATCH or similar

[James8761]

Hi,

I have been given a sample of data. I would like the end user to enter in to two cells (B2 and B3) and the third cell (B4) would give the answer. One of the probelms I have is that I ahve a set of numbers grouped in one cell (B7). I just want to pick one of those numbers. An example is below. Is there a formula that could be placed in cell B4 that would give the correct answer of 4500 please?

Thanks for any help,
James

 

[JustynaMK]

Hi James,

Here's a solution that works fine with Excel 365.

Cell C2 formula to show which row matches your Weight value:

=MAX(IF(ISNUMBER(SEARCH(B2,B7:B10)),ROW(B7:B10)-6,0))

Edit: Alternatively, if you don't have Office 365, this formula seems to work with earlier versions:

=SUMPRODUCT(IF(ISNUMBER(SEARCH(B2,B7:B10)),ROW(B7:B10)-6,0))

Cell D2 formula to show which column matches your Volume value:

=MATCH(B3,C6:G6)

Now you can combine these two in cell B4:

=INDEX(C7:G10,C2,C3)

 

[AhoyNC]

A couple other options:
Note the INDEX formula in cell B4 of the example below is an array formula that probably will need to be entered with CTRL-SHIFT-ENTER in Excel 2019.

Book1
	A	B	C	D	E	F	G
1
2	WT	15
3	VL	400ml
4	Answer	4500		4500
5
6		WT	100ml	200ml	300ml	400ml	500ml
7		10,12,13,14,15,16	3000	3500	4000	4500	5000
8		20,21,23,27,29	3200	3400	3600	3800	4000
9		31,33,35,37,38	3800	3950	4100	4250	4400
Sheet1

Cell Formulas
Range	Formula
B4	B4	=INDEX($C$7:$G$9,MATCH(TRUE,ISNUMBER(SEARCH($B$2,$B$7:$B$9)),0),MATCH($B$3,$C$6:$G$6,0))
D4	D4	=SUMPRODUCT(((ISNUMBER(SEARCH($B$2,$B$7:$B$9))*($C$6:$G$6=$B$3)*($C$7:$G$9))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[James8761]

Hi James,

Here's a solution that works fine with Excel 365.

Cell C2 formula to show which row matches your Weight value:

=MAX(IF(ISNUMBER(SEARCH(B2,B7:B10)),ROW(B7:B10)-6,0))

Edit: Alternatively, if you don't have Office 365, this formula seems to work with earlier versions:

=SUMPRODUCT(IF(ISNUMBER(SEARCH(B2,B7:B10)),ROW(B7:B10)-6,0))

Cell D2 formula to show which column matches your Volume value:

=MATCH(B3,C6:G6)

Now you can combine these two in cell B4:

=INDEX(C7:G10,C2,C3)

View attachment 31834

View attachment 31835

Excellent, thank you very much for this. Does exazctly what I want.

 

[James8761]

A couple other options:
Note the INDEX formula in cell B4 of the example below is an array formula that probably will need to be entered with CTRL-SHIFT-ENTER in Excel 2019.

Book1
	A	B	C	D	E	F	G
1
2	WT	15
3	VL	400ml
4	Answer	4500		4500
5
6		WT	100ml	200ml	300ml	400ml	500ml
7		10,12,13,14,15,16	3000	3500	4000	4500	5000
8		20,21,23,27,29	3200	3400	3600	3800	4000
9		31,33,35,37,38	3800	3950	4100	4250	4400
Sheet1

Cell Formulas
Range	Formula
B4	B4	=INDEX($C$7:$G$9,MATCH(TRUE,ISNUMBER(SEARCH($B$2,$B$7:$B$9)),0),MATCH($B$3,$C$6:$G$6,0))
D4	D4	=SUMPRODUCT(((ISNUMBER(SEARCH($B$2,$B$7:$B$9))*($C$6:$G$6=$B$3)*($C$7:$G$9))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you as well for the response. I shall keep this one as well.