• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index match

T

Thomas Kuriakose

Active Member
Respected Sirs,

I am not able to get an index match or lookup working with a drop down list selection.

Attached is a workbook with three trials of index match to lookup values in K3&K4 in range E4:I8 and provide the result which needs to be multiplied by L3.

1. =INDEX(E4:I8,MATCH(K3,$E$3:$I$3,0),MATCH(K4,$D$4:$D$8,0))*L3 - gives the result for values in E4:E7, but does not give the values from E8:I8.
2. =IFERROR(INDEX(E4:I8,MATCH(K3&K4,INDEX(E3:I3&D4:D8,,),0)),0)*L3 - all results are zero
3. =INDEX(E4:I8,MATCH(K3&K4,E3:I3&D4:D8,0)) - returns #N/A
 
Respected Sirs,

Missed the attachment, post button clicked by error.

Here is the attachment.

Thank you very much for your support,

with regards,
thomas
 

Attachments

  • Index match and lookup.xlsx
    10.9 KB · Views: 6
Respected Sir,

Thank you very much for this correction and your support,

is there a possibility of getting the result without array formula.

Very much appreciated,

with regards,
thomas
 
Thomas Kuriakose said:
1. =INDEX(E4:I8,MATCH(K3,$E$3:$I$3,0),MATCH(K4,$D$4:$D$8,0))*L3 - gives the result for values in E4:E7, but does not give the values from E8:I8.
2. =IFERROR(INDEX(E4:I8,MATCH(K3&K4,INDEX(E3:I3&D4:D8,,),0)),0)*L3 - all results are zero
3. =INDEX(E4:I8,MATCH(K3&K4,E3:I3&D4:D8,0)) - returns #N/A
Click to expand...

Please refer to your 3 formulas, the formula is designed as per the data source layout format in "Table type" and "Database type".

Here's the explanation and details:

1] Type 1 "2 way lookup formula" using in "Table Type Format"
[L4] =INDEX(E4:I8,MATCH(K4,$D$4:$D$8,0),MATCH(K3,$E$3:$I$3,0))*L3

2] Type 2 "2 criteria lookup non-array formula" using in "Database Type Format"
[M4] =IFERROR(INDEX(F12:F36,MATCH(K4&K3,INDEX(D12:D36&E12:E36,,),0)),0)*L3

3] Type 3 "2 criteria lookup array formula" using in "Database Type Format"
[N4] =IFERROR(INDEX(F12:F36,MATCH(K4&K3,D12:D36&E12:E36,0)),0)*L3

65288

Regards
Bosco
 

Attachments

  • 2 way and 2 criteria Lookup (BY).xlsx
    15.5 KB · Views: 8
I like the discussion, especially @bosco_yip's presentation that included data normalisation.
To bring it up to date in an Office 365 context, XLOOKUP offers some additional solutions.
Using nested form
= XLOOKUP(rowName, rowHdr, XLOOKUP(colName, colHdr, data) ) * value
There also exists the implicit intersection form (like @herofox's)
= ( XLOOKUP(rowName, rowHdr, data) XLOOKUP(colName, colHdr, data) ) * value

Since @deciog is willing to consider a calculation over a 2D array, it is also possible to do this with SUMIFS
= value * SUMIFS(data, mask#, 1)
The catch is that the criterion range 'mask' has to be a range reference and not simply an array. With dynamic arrays, the formula
= (colHdr=colName) * (rowHdr=rowName)
can be placed anywhere, even using a hidden sheet, since it will resize automatically.
 
Respected Bosco Sir, Decoig Sir, Peter Sir,

Thank you so much for all the varying insights, guidance and explanations. You are an amazing team.

This truly is the best forum where all the knowledge shared helps in improving our excel skills on a daily basis and improves a lot of efficiency at workplace.

Very much appreciated always,

with regards,
thomas
 
You must log in or register to reply here.
Back
Top