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!!!

Shift 1
Name	First Round	Second Round	Disparity
Joe	26	25	96.15%
Mary	55	38	69.09%
Ken	41	38	92.68%
Shift 2
Name	First Round	Second Round	Disparity
Mark	56	55	98.21%
Larry	25	21	84.00%
Sue	26	20	76.92%
Shift 3
Name	First Round	Second Round	Disparity
Tom	40	25	62.50%
Mellissa	11	8	72.73%
John	27	26	96.30%
Cover Sheet
Name	First Round	Second Round	Disparity
Joe	26	25	96.15%
Ken	41	38	92.68%
Name	First Round	Second Round	Disparity
Mark	56	55	98.21%
Name	First Round	Second Round	Disparity
John	27	26	96.30%

<tbody>
</tbody><colgroup><col><col><col><col></colgroup>

 

[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 :

Monday	a	40
	c	77
	e	43
Tuesday	b	23
	a	87
	c	44
	d	63

<tbody>
</tbody>

What i want for output is something like this

Monday	e	43
Tuesday	a	87

<tbody>
</tbody>

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 :

Monday	a	40
	c	77
	e	43
Tuesday	b	23
	a	87
	c	44
	d	63

<tbody>
</tbody>

What i want for output is something like this

Monday	e	43
Tuesday	a	87

<tbody>
</tbody>

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 !

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Monday	a	40​		1​	3​	Monday	e	43
2​		c	77​		4​	7​	Tuesday	a	87
3​		e	43​
4​	Tuesday	b	23​
5​		a	87​
6​		c	44​
7​		d	63​

<tbody>
</tbody>

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.