Greetings, I am having an issue with the below INDEX Formula
=INDEX(BOBJ!$C$2:$C$606,MATCH(1,INDEX((BOBJ!$A$2:$A$606=B4)*(BOBJ!$B$2:$B$606=D4&""),0),0))
the data it is pulling is formatted as general in BOBJ!$C$2:$C$606
the data being matched to is formatted as General both on the target tab and the source compare tab and is numeric or alpha or mixed: BOBJ!$A$2:$A$606=B4
the last part is formatted as general, and can be numeric or Alpha or mixed. BOBJ!$B$2:$B$606=D4
If the info in the "B4" and D4 is numeric, I do not get any results. If I make them an Alpha, the formula works. If they are is Alphanumeric it works.
Any thoughts?
Brad
Assistance with Index Formula
-
- 4StarLounger
- Posts: 540
- Joined: 30 Mar 2010, 18:49
- Location: United States
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 4StarLounger
- Posts: 540
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Assistance with Index Formula
As requested
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Assistance with Index Formula
How about
=INDEX(BOBJ!$C$2:$C$3,MATCH(1,INDEX((BOBJ!$A$2:$A$3=B3)*(BOBJ!$B$2:$B$3=D3),0),0))
=INDEX(BOBJ!$C$2:$C$3,MATCH(1,INDEX((BOBJ!$A$2:$A$3=B3)*(BOBJ!$B$2:$B$3=D3),0),0))
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 540
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Assistance with Index Formula
Hans,
I think I found the issue. The data was having Type Mismatch issues. Took a bit to determine why. SO thanks for the reply...
I think I found the issue. The data was having Type Mismatch issues. Took a bit to determine why. SO thanks for the reply...
-
- 4StarLounger
- Posts: 540
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Assistance with Index Formula
So now to tag onto this thread.
With this formula, =INDEX(BOBJ!$C$2:$C$606,MATCH(1,INDEX((BOBJ!$A$2:$A$606=B4)*(BOBJ!$B$2:$B$606=D4&""),0),0))
Is there a way to have it match two variables? right now it is ensuring data in BOBJ!$A$2:$A$606=B4 (where B4 is on my primary tab for the resultant info. And the BOBJ!$B$2:$B$606=D4 is a second match (d4 is on my primary tab). I need to ensure another variable is checked for and matched, before grabbing what is in the BOBJ!$C$2:$C$606 field. the additional match would be Col Q on the primary, and col N on the BOBJ tab.
With this formula, =INDEX(BOBJ!$C$2:$C$606,MATCH(1,INDEX((BOBJ!$A$2:$A$606=B4)*(BOBJ!$B$2:$B$606=D4&""),0),0))
Is there a way to have it match two variables? right now it is ensuring data in BOBJ!$A$2:$A$606=B4 (where B4 is on my primary tab for the resultant info. And the BOBJ!$B$2:$B$606=D4 is a second match (d4 is on my primary tab). I need to ensure another variable is checked for and matched, before grabbing what is in the BOBJ!$C$2:$C$606 field. the additional match would be Col Q on the primary, and col N on the BOBJ tab.
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Assistance with Index Formula
What does column N on the BOBJ sheet contain? Text, numbers or a mixture?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 540
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Assistance with Index Formula
Mixture on (primary tab) col Q and Mixture on BOBJ col N .
Thanks,
Thanks,
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Assistance with Index Formula
Try
=INDEX(BOBJ!$C$2:$C$606,MATCH(1,INDEX((BOBJ!$A$2:$A$606=B4)*(BOBJ!$B$2:$B$606=D4&"")*(BOBJ!$N$2:$N$606=Q4&""),0),0))
=INDEX(BOBJ!$C$2:$C$606,MATCH(1,INDEX((BOBJ!$A$2:$A$606=B4)*(BOBJ!$B$2:$B$606=D4&"")*(BOBJ!$N$2:$N$606=Q4&""),0),0))
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 540
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Assistance with Index Formula
Hans,
If I just paste the formula it works, however When I try to adjust the VBA I get : Run-time error '1004': Application-defined or object-defined error.
Here is the original, my adjusted and the formula:
Original:
'Range("BH3:BH" & LastRow9).FormulaR1C1 = "=INDEX(BOBJ!R2C3:R" & LastRow99 & "C3,MATCH(1,INDEX((BOBJ!R2C1:R" & LastRow99 & "C1=RC[-58])*(BOBJ!R2C2:R" & LastRow99 & "C2=RC[-56]&""""),0),0))"
My MOD
Range("BH3:BH" & LastRow9).FormulaR1C1 = "=INDEX(BOBJ!R2C3:R" & LastRow99 & "C3,MATCH(1,INDEX((BOBJ!R2C1:R" & LastRow99 & "C1=RC[-58])*(BOBJ!R2C2:R" & LastRow99 & "C2=RC[-56]&"""")*(BOBJ!R2C14:R" & LastRow99 & "C14=RC[-43]&"""",0),0))"
Your adjusted Formula: I spaced it so I could see how it lined up with the VBA
' =INDEX(BOBJ!$C$2:$C$606, MATCH(1,INDEX((BOBJ!$A$2:$A$606=B4) *(BOBJ!$B$2:$B$606=D4&"") *(BOBJ!$N$2:$N$606=Q4&""),0),0))
If I just paste the formula it works, however When I try to adjust the VBA I get : Run-time error '1004': Application-defined or object-defined error.
Here is the original, my adjusted and the formula:
Original:
'Range("BH3:BH" & LastRow9).FormulaR1C1 = "=INDEX(BOBJ!R2C3:R" & LastRow99 & "C3,MATCH(1,INDEX((BOBJ!R2C1:R" & LastRow99 & "C1=RC[-58])*(BOBJ!R2C2:R" & LastRow99 & "C2=RC[-56]&""""),0),0))"
My MOD
Range("BH3:BH" & LastRow9).FormulaR1C1 = "=INDEX(BOBJ!R2C3:R" & LastRow99 & "C3,MATCH(1,INDEX((BOBJ!R2C1:R" & LastRow99 & "C1=RC[-58])*(BOBJ!R2C2:R" & LastRow99 & "C2=RC[-56]&"""")*(BOBJ!R2C14:R" & LastRow99 & "C14=RC[-43]&"""",0),0))"
Your adjusted Formula: I spaced it so I could see how it lined up with the VBA
' =INDEX(BOBJ!$C$2:$C$606, MATCH(1,INDEX((BOBJ!$A$2:$A$606=B4) *(BOBJ!$B$2:$B$606=D4&"") *(BOBJ!$N$2:$N$606=Q4&""),0),0))
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Assistance with Index Formula
You omitted one closing parenthesis:
Range("BH3:BH" & LastRow9).FormulaR1C1 = "=INDEX(BOBJ!R2C3:R" & LastRow99 & "C3,MATCH(1,INDEX((BOBJ!R2C1:R" & LastRow99 & "C1=RC[-58])*(BOBJ!R2C2:R" & LastRow99 & "C2=RC[-56]&"""")*(BOBJ!R2C14:R" & LastRow99 & "C14=RC[-43]&""""),0),0))"
Range("BH3:BH" & LastRow9).FormulaR1C1 = "=INDEX(BOBJ!R2C3:R" & LastRow99 & "C3,MATCH(1,INDEX((BOBJ!R2C1:R" & LastRow99 & "C1=RC[-58])*(BOBJ!R2C2:R" & LastRow99 & "C2=RC[-56]&"""")*(BOBJ!R2C14:R" & LastRow99 & "C14=RC[-43]&""""),0),0))"
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 540
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Assistance with Index Formula
That was such a silly error on my part.
Thanks!
Thanks!