Reverse nested lookup.

[xtrato]

Hi , Im trying to get the following formula , not sure if im trying too hard...

I want to First Search for Category 1 , 2 or 3 THEN - search for A, B, C Or D (Within that CATEGORY) , and then OUTPUT THE PRODUCT,

This is what im using but its not working:

=INDEX(A2:A4,,MATCH(H8,INDEX(B2:G4,MATCH(H7,B1:G1,0),),0))

Any help i s appreciated.

 

[Phuoc]

Try this:

=INDEX(A2:A4,,MATCH(H8,INDEX(B2:G4,0,MATCH(H7,B1:G1,0),),0))

 

[Peter_SSs]

I think that you need this. As well as the added zero suggested by @Phuoc there are some extra commas that need removal

=INDEX(A2:A4,MATCH(H8,INDEX(B2:G4,0,MATCH(H7,B1:G1,0)),0))

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, If you have a recent Excel version with the FILTER function, you could also do it this way.

=FILTER(A2:A4,FILTER(B2:G4,B1:G1=H7)=H8)

 

[xtrato]

You guys are great ! - this did the trick , im going to try that filter formula!!!

it became a bit big once it was looking for a duplicate number in a Duplicate RANK! in another sheet. but it worked nontheless !

=IFERROR(INDEX(TrueCal!$AM$8:$AM$100,XMATCH(A11,INDEX(TrueCal!$AN$8:$AS$100,0,XMATCH($B$7,TrueCal!$AN$7:$AS$7,0)),0,)),INDEX(TrueCal!$AM$8:$AM$100,XMATCH(A9,INDEX(TrueCal!$AN$8:$AS$100,0,XMATCH($B$7,TrueCal!$AN$7:$AS$7,0)),0,-1)))

 

[Peter_SSs]

it became a bit big once it was looking for a duplicate number in a Duplicate RANK! in another sheet. but it worked nontheless !

=IFERROR(INDEX(TrueCal!$AM$8:$AM$100,XMATCH(A11,INDEX(TrueCal!$AN$8:$AS$100,0,XMATCH($B$7,TrueCal!$AN$7:$AS$7,0)),0,)),INDEX(TrueCal!$AM$8:$AM$100,XMATCH(A9,INDEX(TrueCal!$AN$8:$AS$100,0,XMATCH($B$7,TrueCal!$AN$7:$AS$7,0)),0,-1)))

.. but you can write that formula in a much shorter way with 365. I think that you will find this produces the same results.

=LET(r,TrueCal!$AM$7:$AS$100,c,INDEX(r,0,XMATCH($B$7,TAKE(r,1))),INDEX(r,IFNA(XMATCH(A11,c),XMATCH(A9,c,0,-1)),1))

 

[xtrato]

Ive never used LET - but seems its assigning a letter a formula in essense?i ve applied it and it works great! ... would this be better (performance-wise) than the filter function you mentioned above?

=FILTER(A2:A4,FILTER(B2:G4,B1:G1=H7)=H8)

My sheet is massive and i'd take even a 1% increase in performance.

.. but you can write that formula in a much shorter way with 365. I think that you will find this produces the same results.

=LET(r,TrueCal!$AM$7:$AS$100,c,INDEX(r,0,XMATCH($B$7,TAKE(r,1))),INDEX(r,IFNA(XMATCH(A11,c),XMATCH(A9,c,0,-1)),1))

 

[xtrato]

.. but you can write that formula in a much shorter way with 365. I think that you will find this produces the same results.

=LET(r,TrueCal!$AM$7:$AS$100,c,INDEX(r,0,XMATCH($B$7,TAKE(r,1))),INDEX(r,IFNA(XMATCH(A11,c),XMATCH(A9,c,0,-1)),1))

Quick questions , how can i re-write the following formula with LET function?

=IFERROR(INDEX('32'!$TC$1:$WF$500,XMATCH($CZ5,'32'!$TC$1:$TC$500),XMATCH($CZ$2,'32'!$TC$1:$WF$1)),0)

it seems its a LOT More clean than this

 

[Peter_SSs]

would this be better (performance-wise) than the filter function you mentioned above?

I don't know but I suspect that INDEX/XMATCH might be faster than FILTER/FILTER. You would have to check with your data to be sure.

how can i re-write the following formula with LET function?

Try

=LET(r,'32'!$TC$1:$WF$500,IFERROR(INDEX(r,XMATCH($CZ5,TAKE(r,,1)),XMATCH($CZ$2,TAKE(r,1))),0))

 

[xtrato]

Thank you Very much!! it was all very helpful !!