offset and match formula to extract data from another sheet

[JEAN1972]

How to extract data from specific columns headers in another sheet using offset and match.

That is lookup a reference from data sheet and extract data from database as per specific columns headers

However I know how do it with index and match but I am trying to learn the technique offset and match

Can anyone assist and explain how it works as per attached sheet as example

[AlKey]

Enter in C2 and copy across and down

Please Login or Register  to view this content.

here is with OFFSET

Please Login or Register  to view this content.

[AliGW]

I can't see OFFSET or MATCH in that formula ...

[JEAN1972]

Alkey thank you for the formula, working as requested .

The ,, represents the no of rows to move but ,1 at end of formula means what

Please Login or Register  to view this content.

Also from this site but I saw a sample of their formula of offset and match but it is hard coded .

Can this technique applied to my sample file

Please Login or Register  to view this content.

[AlKey]

Hi JEAN,
The 1 is Height (Optional). The height, in number of rows, that you want the returned reference to be. Height must be a positive number.

[JEAN1972]

Thanks for help

I have tried using the offset and match using different approach. getting same results .

Any suggestions would be welcome

[AlKey]

I am not quite sure what results you expect to see. If you need to return Barcode and Statistic no columns with use of OFFSET and MATCH try this:
Enter in E2 and copy across to F2 and down

Please Login or Register  to view this content.

[shukla.ankur281190]

DataBase

Row\Col	A	B	C	D
1	Item no	Barcode	Statistic no	Value
2	SP8100120	8711295601811	95030021	64.8
3	SP8100130	8711295602313	95030021	388.8
4	S30000280	8711295188534	95030081	777.6
5	S30820050	8719202037258	95030070	243
6	S34080700	8718158920683	95030095	108
7	S34089310	8711295723179	95030070	268.8
8	S34089350	8711295723803	95030070	345.6
9	AAE042030	8719202228984	95051090	277.2

Data Sheet

Row\Col	A	B	C	D	E	F	G	H	I
1	Item no	Item description	Barcode	Statistic no	Value		Expected Result
2	SP8100120	DOLL WITH SOUND 30CM 2ASS	8711295601811	95030021	64.8		8.71E+12	95030021	64.8
3	SP8100130	DOLL WITH SOUND 25CM 2ASS	8711295602313	95030021	388.8		8.71E+12	95030021	388.8
4	S30000280	SPACE GUN PP BATTERY OPERATED	8711295188534	95030081	777.6		8.71E+12	95030081	777.6
5	S30820050	SHOPPINGBASKET 6ASS GROCERY	8719202037258	95030070	243		8.72E+12	95030070	243
6	S34080700	MOTOR TOWN PP SET OF 31PCS INC	8718158920683	95030095	108		8.72E+12	95030095	108
7	S34089310	KITCHEN TOOL SET PP 10PCS 2ASS	8711295723179	95030070	268.8		8.71E+12	95030070	268.8
8	S34089350	DOCTORSET PP 6PCS IN PP CASE A	8711295723803	95030070	345.6		8.71E+12	95030070	345.6
9	AAE042030	DIADEM CHRISTMAS 4ASS DESIGNS	8719202228984	95051090	277.2		8.72E+12	95051090	277.2

C2=OFFSET(DTABASE!B$1,MATCH($A2,DTABASE!$A:$A,0)-1,,,)

Copy towards D and down.

E2

Please Login or Register  to view this content.

Both copy down.!!!

As per you Post#1 attached file.

[JEAN1972]

@Alkey @shukla.ankur281190 thank you for assistance