VLOOKUP but using 2 conditions

[NickPDC]

Hi guys,

Apologies if the title isn't very descriptive of the problem, but I will give this my best shot.

Attached is a sample workbook I have been trying to get working.

Basically, when an 'AM Number' is typed into cells F6-H8, the fields on the left hand side, for Disa X, Disa B and Disa A need to populate.

The 'MachineData' tab will supply more information, as this is the sample data.

For one AM number, there can be several Disa values. Meaning for example:

When - AM001A is typed into F6, the values underneath DISA B and DISA X must populate with the following:

Machine GrossCastingWeight MouldsPerHour PouringTime CupLevel CastingsPerMould
DISA B 16.18 360 8 1 1
DISA X 29.16 274 8.5 1 2

Likewise, when AM015A is typed into F6, only the values under DISA A must populate with the following:

Machine GrossCastingWeight MouldsPerHour PouringTime CupLevel CastingsPerMould
DISA A 12.57 360 6.4 1 1

If someone can make sense of that, and come up with a working solution that would be absolutely spectacular.

Thanks,

Nick


Sample Workbook.xls

[veejar]

Hi,

PLease find the modified workbook which hopefully resolves you issue

Regards,

Veejar

[NickPDC]

Thanks for the help VeeJar, that works perfectly in the sample worksheet.

Unfortunately it doesn't translate perfectly into my working document.

Is there no way a 2 dimensional lookup can be used? That is what I have been trying.

Thanks,

Nick

[martindwilson]

this is essentialy the same but not using named ranges just join your 2 columns with & in an index
then match f6&a1 in that index.

[NickPDC]

this is essentialy the same but not using named ranges just join your 2 columns with & in an index
then match f6&a1 in that index.

Thanks very much Martin.

When I plugged that into the final document I couldn't get it to work.

My error, not yours.

This is what I was left with:

=INDEX('\\pdcdata\c$\Syteline Backup\[Export.xlsx]MachineData'!$A:$N,MATCH($H$2&$A$17,INDEX('\\pdcdata\c$\Syteline Backup\[Export.xlsx]MachineData'!A1:A2000&'\\pdcdata\c$\Syteline Backup\[Export.xlsx]MachineData'!$B1:B2000,0),ROWS(B18)))

Export.xlsx is the document where the data is stored. It looks exactly the same as the sample data I posted onto that worksheet. There are about 2000 records that need looking up.

The AM number is in cell H2.
The DISA reference is in cell A17.

Any idea as to where I am going wrong?

[martindwilson]

its constructed wrongly
ok taking out '\\pdcdata\c$\Syteline Backup\[Export.xlsx]MachineData'! for the moment

=INDEX($A:$N,MATCH($H$2&$A$17,INDEX(A1:A2000&$B1:B2000,0),ROWS(B18)))

should read
=INDEX($A:$N,MATCH($H$2&$A$17,INDEX($A$1:$A$2000&$B$1:$B$2000,0),0),rows($a$1:a1))
or
=INDEX('\\pdcdata\c$\Syteline Backup\[Export.xlsx]MachineData'!$A:$N,MATCH($H$2&$A$17,INDEX('\\pdcdata\c$\Syteline Backup\[Export.xlsx]MachineData'!$A$1:$A$2000&'\\pdcdata\c$\Syteline Backup\[Export.xlsx]MachineData'!$B$1:$B$2000,0),0),rows($a$1:a1))
this bit
rows($a$1:a1) =1
and when dragged down
ROWS($A$1:A2) =2
=ROWS($A$1:A3)=3
so since index syntax is
=index(a:n,row,column)
row is determined by the match part
MATCH($H$2&$A$17,INDEX($A$1:$A$2000&$B$1:$B$2000,0),0)
and column is determined by
rows($a$1:a1)

[NickPDC]

its constructed wrongly
ok taking out '\\pdcdata\c$\Syteline Backup\[Export.xlsx]MachineData'! for the moment

=INDEX($A:$N,MATCH($H$2&$A$17,INDEX(A1:A2000&$B1:B2000,0),ROWS(B18)))

should read
=INDEX($A:$N,MATCH($H$2&$A$17,INDEX($A$1:$A$2000&$B$1:$B$2000,0),0),rows($a$1:a1))
or
=INDEX('\\pdcdata\c$\Syteline Backup\[Export.xlsx]MachineData'!$A:$N,MATCH($H$2&$A$17,INDEX('\\pdcdata\c$\Syteline Backup\[Export.xlsx]MachineData'!$A$1:$A$2000&'\\pdcdata\c$\Syteline Backup\[Export.xlsx]MachineData'!$B$1:$B$2000,0),0),rows($a$1:a1))
this bit
rows($a$1:a1) =1
and when dragged down
ROWS($A$1:A2) =2
=ROWS($A$1:A3)=3
so since index syntax is
=index(a:n,row,column)
row is determined by the match part
MATCH($H$2&$A$17,INDEX($A$1:$A$2000&$B$1:$B$2000,0),0)
and column is determined by
rows($a$1:a1)

Absolutely brilliant!

I cannot thank you enough.

SOLVED!

Thanks,

Nick