Index / match for lookup

[HelenL]

INDEX/MATCH
Help please ! I need to scan left and right in another worksheet to look up a number value but the lines concerned use the same reference number, but with a different subset number. How to look up Number Value with a condition, so that this subset number is taken into account, otherwise returns the same value for all 3 lines??
Example
Reference subset number value (to be retrieved from another worksheet)
60002 0 32.00
60002 1 968.00
60002 5 180.00

 

[Matt_Chelmsford]

could you upload an example of the matrix, with the desired results?

Regards

Matthew

 

[markmzz]

Maybe this:

Layout

Reference	Subset	Value	Sheet2	Reference	Subset	Value	Sheet1
60002	0	32.00		60002	5	180.00
60002	1	968.00		60002	1	968.00
60002	5	180.00
*********	******	******	********	*********	******	******	********

<tbody>
</tbody>

Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

=INDEX(Sheet02!$C$2:$C$4,MATCH(A2&B2,Sheet02!$A$2:$A$4&Sheet02!$B$2:$B$4,0))

Markmzz

 

[HelenL]

Thank you Markmzz. very much appreciated. will give it a go now and let you know.

 

[HelenL]

Re: INDEX MATCH LOOKUP returning wrong value

=INDEX(Main!M2:M9, MATCH(Main!B2&Main!E2,Main!B2:B6&Main!E2:E6,0))
M2:M9 = CValues range of cells (without column label)
B2 = subNumber E2=Aquisition B2:B6 does not include column heading lable, ditto E2:E6 range

Using above formula returns values listed in the order of the master data only. Any thoughts please why ?
Making the ranges absolute gives Value error. Other attempts gave same value for all sub numbers within the Acquisition.
Data in main table is in different order and has more columns than Sheet 2. Sheet 2 is an extract of some of the column headings and needs the CValue is to be inserted. Some column headings are to the right and some to the left of the CValue on Sheet 2.

Set	subNumber	AssetClass	AssetDesc	Acquisition	CValue	[ Main table ]
20000	1	5001	ade	123444	5.00
20016	5	5001	ben	111789	200.00
20000	2	5001	ade	123444	4.00
20016	0	5001	ben	111789	500.00
20016	4	5001	ben	111789	60.00

<tbody>
</tbody>

 

[markmzz]

Re: INDEX MATCH LOOKUP returning wrong value

=INDEX(Main!M2:M9, MATCH(Main!B2&Main!E2,Main!B2:B6&Main!E2:E6,0))
M2:M9 = CValues range of cells (without column label)
B2 = subNumber E2=Aquisition B2:B6 does not include column heading lable, ditto E2:E6 range

Using above formula returns values listed in the order of the master data only. Any thoughts please why ?
Making the ranges absolute gives Value error. Other attempts gave same value for all sub numbers within the Acquisition.
Data in main table is in different order and has more columns than Sheet 2. Sheet 2 is an extract of some of the column headings and needs the CValue is to be inserted. Some column headings are to the right and some to the left of the CValue on Sheet 2.

Hi HelenL,

Here the formula works (with your layout above). Look at this:

Layout

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Set	subNumber	AssetClass	AssetDesc	Acquisition								Cvalue	Main
2	20000	1	5001	ade	123444								5.00
3	20016	5	5001	ben	111789								200.00
4	20000	2	5001	ade	123444								4.00
5	20016	0	5001	ben	111789								500.00
6	20016	4	5001	ben	111789								60.00
****	*******	***********	**********	*********	**********	**	**	**	**	**	**	**	******	*****

<tbody>
</tbody>

	A	B	C	D	E	F	G
1		subNumber			Aquisition	Result	Sheet2
2		2			123444	4.00
3		0			111789	500.00
4		4			111789	60.00
****	**	***********	**	**	*********	******	*******

<tbody>
</tbody>

Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula (in Sheet2)

[COLOR="#FF0000"]F2[/COLOR]-> =INDEX(Main!$M$2:$M$6,MATCH([COLOR="#FF0000"]B2&E2[/COLOR],Main!$B$2:$B$6&Main!$E$2:$E$6,0))

Markmzz

 

[HelenL]

Re: INDEX MATCH LOOKUP returning wrong value

Hi Markmzz
Thank you, much appreciated.
You will notice that you have chosen 3 lines in a row from the master data for Sheet 2, but this is the problem, it is returning values regardless of what line is next. I couldnt figure out how to get a second table for the Sheet 2 example. You will see if you try formula above and the lines do not correspond to master data order, you get those values from that point forward. If you swap the middle entry for the first entry in table 2, you will see what I mean. Please dont give up! Many thanks

 

[markmzz]

Re: INDEX MATCH LOOKUP returning wrong value

Hi Markmzz
Thank you, much appreciated.
You will notice that you have chosen 3 lines in a row from the master data for Sheet 2, but this is the problem, it is returning values regardless of what line is next. I couldnt figure out how to get a second table for the Sheet 2 example. You will see if you try formula above and the lines do not correspond to master data order, you get those values from that point forward. If you swap the middle entry for the first entry in table 2, you will see what I mean. Please dont give up! Many thanks

Hi HelenL,

Sorry, but I didn't understand what you want.

Could you post what you have and want you want (with data examples)?

Markmzz