CODEWITHSUNDEEP
excelexcel-formulaexcel-2007excel-2010vlookup
sau6402
sau6402

Reputation: 33

Multiple value lookup accross excel sheets

I have a master worksheet and a Extract worksheet. I want to extract data from Extract sheet and present it as follows in the master sheet.

Data is as follows:

Extract Sheet
--------------------------------------
Fruit    Nutrients    Proportion
Apple    Calcium      10
Apple    Vitamin B    20
Mango    Vitamin A    50
Lemon    Vitamin C    30
Lemon    Vitamin A    10

Master Sheet
--------------------------------------------------------
Fruit    Calcium    Vitamin A    Vitamin B    Vitamin C
Apple    10         0             20           0
Mango    0          50            0            0
Lemon    0          10            0            30

I have used Excel Array functions SMALL and INDEX but I am unable to get the Proportion value in the master table. I keep getting #NUM error

Please help me with the same and guide.

Thanks in advance.

Upvotes: 1

Views: 262

Answers (2)

Jur Pertin
Jur Pertin

Reputation: 564

Try this:

=IFERROR(LOOKUP(2,1/(('Extract '!$A$2:$A$6=$A2)*('Extract '!$B$2:$B$6=B$1)),'Extract '!$C$2:$C$6),0)

Upvotes: 0

user4039065
user4039065

Reputation:

I've put your sample data and results into a single worksheets as per the following image but you should be able to move the Extract to another worksheet and transcribe the formula for your own purposes.

    enter image description here

The standard formula in F2 is =IFERROR(INDEX($C$2:$C$6,MIN(INDEX(ROW($1:$5)+(($A$2:$A$6<>$E2)+($B$2:$B$6<>F$1))*1E+99,,))),0). Fill both right and down as necessary. You mentioned using SMALL but you have no duplicated values across the two criteria columns so I think this is closer to what you require. Post back a comment if you do need a SMALL function with k picking the first, second, etc.

Upvotes: 1

Related Questions