VLOOKUP - Multiple Criteria

[gregula82]

Hi Everyone,

I was wondering whether anyone knew of a way of performing a VLOOKUP function which has multiple criteria. e.g. the lookup value would have 3 separate criteria and then you put the table array in and the column index.

Any ideas???

Thanks.

Greg.

 

[ScottVP]

If VLOOKUP is expected to return a text value when successful...

=IFERROR(LOOKUP(REPT("z",255),
  CHOOSE({1,2},VLOOKUP("STT1RM2",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0),
   VLOOKUP("STT1RM1",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0))),"")

If VLOOKUP is expected to return a number when successful...

=IFERROR(LOOKUP(9.99999999999999E+307,
  CHOOSE({1,2},VLOOKUP("STT1RM2",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0),
   VLOOKUP("STT1RM1",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0))),"")

Hope this helps.

PERFECT!!! Thank you very much!!!

 

[ScottVP]

Ok. Not sure if this can be done. I worked on this some and couldn't get close to bringing a percentile close.

I have three separate sheets with a cover sheet in which I would like to bring the top 95% of shift work to the cover sheet from each of the other sheets. This can be in one group with the sheet number in the foremost left colomn or it can be all separate as I list it below. What is key here is that I would like to retrieve the whole row, if this is possible.

Appreciate the knowledge, guys!!!

[TABLE="width: 333"]
<tbody>[TR]
[TD]Shift 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]First Round[/TD]
[TD]Second Round[/TD]
[TD]Disparity[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]96.15%[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]69.09%[/TD]
[/TR]
[TR]
[TD]Ken[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]92.68%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shift 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]First Round[/TD]
[TD]Second Round[/TD]
[TD]Disparity[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]98.21%[/TD]
[/TR]
[TR]
[TD]Larry[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]84.00%[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]76.92%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shift 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]First Round[/TD]
[TD]Second Round[/TD]
[TD]Disparity[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]62.50%[/TD]
[/TR]
[TR]
[TD]Mellissa[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]72.73%[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]96.30%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Cover Sheet[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]First Round[/TD]
[TD]Second Round[/TD]
[TD]Disparity[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]96.15%[/TD]
[/TR]
[TR]
[TD]Ken[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]92.68%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]First Round[/TD]
[TD]Second Round[/TD]
[TD]Disparity[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]98.21%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]First Round[/TD]
[TD]Second Round[/TD]
[TD]Disparity[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]96.30%[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

 

[seilent]

Kinda feel bad for reviving old thread, but i think better than creating new post.
I've been reading since page 1 but still can't figure how to figure this :


[TABLE="class: grid, width: 1, align: left"]
<tbody>[TR]
[TD]Monday
[/TD]
[TD]a
[/TD]
[TD]40
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]c
[/TD]
[TD]77
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]e
[/TD]
[TD]43
[/TD]
[/TR]
[TR]
[TD]Tuesday
[/TD]
[TD]b
[/TD]
[TD]23
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]a
[/TD]
[TD]87
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]c
[/TD]
[TD]44
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]d
[/TD]
[TD]63
[/TD]
[/TR]
</tbody>[/TABLE]












What i want for output is something like this

[TABLE="class: grid, width: 1, align: left"]
<tbody>[TR]
[TD]Monday
[/TD]
[TD]e
[/TD]
[TD]43
[/TD]
[/TR]
[TR]
[TD]Tuesday
[/TD]
[TD]a
[/TD]
[TD]87
[/TD]
[/TR]
</tbody>[/TABLE]




i tried to follow the one in page 7 but no luck since this one is unsorted, should be doable if i fill every blank cell, but is there any way without doing that? Thanks !

 

[Aladin Akyurek]

Kinda feel bad for reviving old thread, but i think better than creating new post.
I've been reading since page 1 but still can't figure how to figure this :


[TABLE="class: grid, width: 1, align: left"]
<tbody>[TR]
[TD]Monday[/TD]
[TD]a[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]c[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]e[/TD]
[TD]43[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]b[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]a[/TD]
[TD]87[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]c[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]d[/TD]
[TD]63[/TD]
[/TR]
</tbody>[/TABLE]












What i want for output is something like this

[TABLE="class: grid, width: 1, align: left"]
<tbody>[TR]
[TD]Monday[/TD]
[TD]e[/TD]
[TD]43[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]a[/TD]
[TD]87[/TD]
[/TR]
</tbody>[/TABLE]




i tried to follow the one in page 7 but no luck since this one is unsorted, should be doable if i fill every blank cell, but is there any way without doing that? Thanks !

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Monday[/TD]
[TD]a[/TD]
[TD]

40​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

3​

[/TD]
[TD] Monday[/TD]
[TD] e[/TD]
[TD] 43[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD]c[/TD]
[TD]

77​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]

7​

[/TD]
[TD] Tuesday[/TD]
[TD] a[/TD]
[TD] 87[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD][/TD]
[TD]e[/TD]
[TD]

43​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Tuesday[/TD]
[TD]b[/TD]
[TD]

23​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD]a[/TD]
[TD]

87​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD]c[/TD]
[TD]

44​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD]d[/TD]
[TD]

63​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In E1 enter and copy down:

=MATCH(G1,$A$1:$A$7,0)<strike></strike>

In F1 control+shift+enter, not just enter, and copy down:

=MIN(MIN(IF(ROW($A$1:$A$7)-ROW($A$1)+1>MATCH(G1,$A$1:$A$7,0),
    IF(1-($A$1:$A$7=""),ROW($A$1:$A$7)-ROW($A$1)+1,9.99999999999999E+307)))-1,
    ROWS($A$1:$A$7))
<strike></strike>

In E1 enter and copy down:

=VLOOKUP(H1,OFFSET(INDEX($B$1:$B$7,E1),0,0,F1-E1+1,2),2,0)

<strike></strike>

 

[seilent]

snip

omg this really works, may i ask what is F1 and F2 for? i can't really seems to figure how.
But the formula above indeed works, thanks a lot !

 

[Aladin Akyurek]

omg this really works, may i ask what is F1 and F2 for? i can't really seems to figure how.

E1:F1 allows us to compute the borders (location) of the table that involves the value of G1. VLOOKUP does a look up into the table the OFFSET bit constructs from the border values in E1:F1. Copied down, this process cascades for all G-values.

But the formula above indeed works, thanks a lot !

You are welcome.

 

[seilent]

E1:F1 allows us to compute the borders (location) of the table that involves the value of G1. VLOOKUP does a look up into the table the OFFSET bit constructs from the border values in E1:F1. Copied down, this process cascades for all G-values.

ohhh everything makes sense now, i thought bout this before but never able to put it down to formulas, thanks again for explanation !

 

[Aladin Akyurek]

ohhh everything makes sense now, i thought bout this before but never able to put it down to formulas, thanks again for explanation !

My pleasure.

 

[seilent]

From the formula above, now i'm trying to do a lookup for every fruit that sold in day 1 from shop1 and shop2, then put it at daily sheet report without any duplicate.
but it ends up very ugly due to the veeery long formula, and i have like 30+ shops to be listed.
Ii can't really figure to make it simpler.
Is there any way to make this better?
Because i need it to automatically update whenever there's change in fruit or quantity.
Below is the excel file :

http://seilent.org/data/sample.xlsx

Sorry I think i'm asking too much
Thanks !

 

[Aladin Akyurek]

Please try to post a formula-free scaled-down sample along with the desired results directly on this board.
In order to post an Excel readable, small sample, you might want to run one of the methods listed in the following: Attachments or using this add-in:https://app.box.com/s/soezox25h3w0q5s4rcyl.