Index and Match

[Steved]

Hello from Steved

The Below Is giving me a #VALUE! What is required please to give me a result.

I need to use 2 columns in each worksheet to get a result.

Index B4:C4 "In current worksheet"

Match $C$2:$D$5000 in ""Duties Mon-Fri Worksheet""

Result $B$2:$B5000 in "Duties Mon-Fri Worksheet"

=INDEX('Duties Mon-Fri'!$B$2:$B5000,MATCH(B4:C4,'Duties
Mon-Fri'!$C$2:$D$5000,0))

Current Worksheet contains the values

Col B:B and C:C
8431 and 510

Duties Mon-Fri Worksheet contains the values

Col C:C and D
8431 and 510

The result is in COL B:B in Duties Mon-Fri Worksheet

The result should be 4301 but it gives me #VALUE!

Thankyou.

 

[marcus]

Hi Steved

From what I gather you have got your data muddled up. You are trying
to match an item then move backwards and the item you are matching is
not in the idexed range. If you want to move backwards (you said the
item you were looking for was in Col B of the Duties sheet) you need
to add an offset funciton.

Here is the code which sort out the problem. Hope it helps

=OFFSET(INDEX('Duties Mon-Fri Worksheet'!$C$2:$D$5000,MATCH(B4,'Duties
Mon-Fri Worksheet'!$C$2:$C$5000,1),2),,-2)

Index - Contains the range you want to look up with the Matching item
in the left most column.

Match - contains the item you want to match, the column your matched
items will appear in and the number 1 for an exact match.

Offset is uesd to move one column back to Col B from your lookup
region.


Take care

Marcus

 

[Jacob Skaria]

You have multiple conditions to match. You will need to use a array formula
as below

In current worksheet

B4 = 8431
C4 = 510

In D4 enter the formula (all in one line). Please note that this is an array
formula. Within the cell in edit mode (F2) paste this formula and press
Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you
can notice the curly braces at both ends like "{=<formula>}"

=INDEX('Duties Mon-Fri'!$B$2:$B$5000,MATCH(1,('Duties
Mon-Fri'!$C$2:$C$5000=B4)*('Duties Mon-Fri'!$D$2:$D$5000=C4),0))


If this post helps click Yes

 

[Steved]

Hello Jacob

Thankyou very much for solving and working on my Issue, It works perfectly.

Cheers

Steved