Assistance with Index Formula

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Assistance with Index Formula

Post by bradjedis »

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

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Assistance with Index Formula

Post by HansV »

Could you attach a small sample workbook?
Best wishes,
Hans

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Assistance with Index Formula

Post by bradjedis »

As requested
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Assistance with Index Formula

Post by HansV »

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))
Best wishes,
Hans

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Assistance with Index Formula

Post by bradjedis »

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...

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Assistance with Index Formula

Post by bradjedis »

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.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Assistance with Index Formula

Post by HansV »

What does column N on the BOBJ sheet contain? Text, numbers or a mixture?
Best wishes,
Hans

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Assistance with Index Formula

Post by bradjedis »

Mixture on (primary tab) col Q and Mixture on BOBJ col N .


Thanks,

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Assistance with Index Formula

Post by HansV »

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))
Best wishes,
Hans

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Assistance with Index Formula

Post by bradjedis »

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))

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Assistance with Index Formula

Post by HansV »

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))"
Best wishes,
Hans

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Assistance with Index Formula

Post by bradjedis »

That was such a silly error on my part.

Thanks!