Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index and Match
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: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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index and Match
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index and Match
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 --------------- Jacob Skaria "Steved" wrote: 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: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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index and Match
Hello Jacob Thankyou very much for solving and working on my Issue, It works perfectly. Cheers Steved "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Steved" wrote: 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: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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions |