Vlookup with either two lookup value/Column

[nurulnabihahabdullah]

Column 1	Column 2
Chicken	-
Duck	-
Dog	-
Butterfly	Snake
-	Pig
-	Ant

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

Table
Chicken	10
Duck	20
Dog	30
Snake	40
Bear	50
Elephant	60
Ant	70
Pig	80

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

I have a question.
what the formula if my lookup value either two column.
=vlookup(or(column 1,column 2),table,2,true).
But or function cannot be use here.
Help me

 

[FDibbins]

I don't understand what you want, can you show some expected outcomes?

 

[nurulnabihahabdullah]

I don't understand what you want, can you show some expected outcomes?

Table
Chicken	10
Duck	20
Dog	30
Snake	40
Bear	50
Elephant	60
Ant	70
Pig	80

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

Column 1	Column 2	Outcome
Chicken	-	10
Duck	-	20
Dog	-	30
Butterfly	Snake	40
-	Pig	80
-	Ant	70

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

the outcome should be in third column

 

[FDibbins]

Try this...
=IFERROR(VLOOKUP(A12,$A$2:$B$9,2,0),VLOOKUP(B12,$A$2:$B$9,2,0))
copied down

 

[Aladin Akyurek]

Table			Column 1	Column 2	Outcome
Chicken	10			Chicken	-	10
Duck	20			Duck	-	20
Dog	30			Dog	-	30
Snake	40			Butterfly	Snake	40
Bear	50			-	Pig	80
Elephant	60			-	Ant	70
Ant	70
Pig	80

<tbody>
</tbody>

In G2 just enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH($A$2:$A$9,E2&"|"&F2),$B$2:$B$9)

If both column 1 and column 2 values do exit in the table, the number corresponding to one of them is displayed. If this is not the outcome that you want, try the following instead:

In G2 just enter and copy down:

=SUMPRODUCT(SUMIFS($B$2:$B$9,$A$2:$A$9,$E2:$F2))

 

[bosco_yip]

Or this.....

In G2, copied down :

=VLOOKUP(IF(COUNTIF(A$2:A$9,E2),E2,F2),A$2:B$9,2,0)

Regards
Bosco

 

[nurulnabihahabdullah]

or this.....

In g2, copied down :

=vlookup(if(countif(a$2:a$9,e2),e2,f2),a$2:b$9,2,0)

regards
bosco

this is what i want.
Omg!! Youre so awesome!!!!
Thank you so much!!
May god bless you!!!!

 

[Aladin Akyurek]

Just curious: Does that give a different result from:

=LOOKUP(9.99999999999999E+307,SEARCH($A$2:$A$9,E2&"|"&F2),$B$2:$B$9)

which is proposed in post #5 ? If so, when?

 

[nurulnabihahabdullah]

Just curious: Does that give a different result from:

=LOOKUP(9.99999999999999E+307,SEARCH($A$2:$A$9,E2&"|"&F2),$B$2:$B$9)

which is proposed in post #5 ? If so, when?

Sorry but i dont get it.
can you give some example

 

[Aladin Akyurek]

Sorry but i dont get it.
can you give some example

I was asking for an example for which

=LOOKUP(9.99999999999999E+307,SEARCH($A$2:$A$9,E2&"|"&F2),$B$2:$B$9)

would not work as intended.

You have probably not tried out this suggestion... Here is the workbook which implements this formula:

https://www.dropbox.com/s/xqomm5lmw... with either two lookup valueColumn.xlsx?dl=0