CODEWITHSUNDEEP
excelindexingexcel-formulamatch
B.Germ
B.Germ

Reputation: 105

Index and match in excel

I have two sheets, I want to retrive data from the first sheet according to 3 conditions: Month in row1, conditions in row 2, and operations from column B. Each shown in the table: each location have the same two operations. The data

In sheet 2, I want to select the month from the list (Data validation, the months are linked from the table) then the data of that month only will be shown. I used the following code:

=INDEX(Sheet1!B1:K6,MATCH((B1= Sheet1!C1:K1)*(E2=Sheet1!C2:K2),0),MATCH(B3,Sheet1!B1:B6,0))

As seen in the picture: Retrive data

But I get an error that the data is not found even though I am sure it's there. Please guide me what did I do wrong?

UPDATE: I used another code (This is C3): 

=INDEX(Sheet1!C3:K3,MATCH(B1,Sheet1!C1:K1,0),MATCH(C2,Sheet1!C2:K2,0))

It worked, I only change te data range in each cell and the option (H, W, M with month). The weird thing is that it only works for January!! January

But when I select Feb or March I get: Feb

I feel confused what is the reason and how to fix it?

Upvotes: 0

Views: 203

Answers (2)

chris neilsen
chris neilsen

Reputation: 53126

=INDEX(Sheet1!$A:$K,ROW(),MATCH($D$1,Sheet1!$1:$1,0)+COLUMN()-3)

How it works:

MATCH($D$1,Sheet1!$1:$1,0)

finds the column of the selected month in Sheet1

+COLUMN()-3

offsets from that column to get H, W or M column

ROW()

gives same rows in Sheet1 and destination sheet

INDEX(...)

returns the result

Upvotes: 2

p._phidot_
p._phidot_

Reputation: 1950

Do in Sheet2 C3 :

=INDEX(OFFSET(Sheet1!$C$3:$F$4,MATCH(OFFSET(Sheet2!$A3,IF(Sheet2!$A3="",-1,0),0),Sheet1!$A$3:$A$8,0)-1,MATCH(Sheet2!$B$1,Sheet1!$C$1:$N$1,0)-1),MATCH(Sheet2!$B3,Sheet1!$B$3:$B$4,0),MATCH(C$2,Sheet1!$C$2:$E$2,0))

Idea : use offset to 'determine' the area for OP,CR & H,W,M search. put an If+offset condition to 'shift' the sheet2,columnA reference if it is blank.

Please share if it works/not. ( :

Upvotes: 0

Related Questions