Vlookup with either two lookup value/Column

[nurulnabihahabdullah]

[TABLE="class: grid, width: 129"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[/TR]
[TR]
[TD]Chicken[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Duck[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Butterfly[/TD]
[TD]Snake[/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD]Pig[/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD]Ant[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 128, colspan: 2"]Table[/TD]
[/TR]
[TR]
[TD]Chicken[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Duck[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Snake[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Bear[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Elephant[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]Ant[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD]Pig[/TD]
[TD="align: right"]80[/TD]
[/TR]
</tbody>[/TABLE]




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="class: grid, width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 128, colspan: 2"]Table[/TD]
[/TR]
[TR]
[TD="width: 64"]Chicken[/TD]
[TD="width: 64"]10[/TD]
[/TR]
[TR]
[TD="width: 64"]Duck[/TD]
[TD="width: 64"]20[/TD]
[/TR]
[TR]
[TD="width: 64"]Dog[/TD]
[TD="width: 64"]30[/TD]
[/TR]
[TR]
[TD="width: 64"]Snake[/TD]
[TD="width: 64"]40[/TD]
[/TR]
[TR]
[TD="width: 64"]Bear[/TD]
[TD="width: 64"]50[/TD]
[/TR]
[TR]
[TD="width: 64"]Elephant[/TD]
[TD="width: 64"]60[/TD]
[/TR]
[TR]
[TD="width: 64"]Ant[/TD]
[TD="width: 64"]70[/TD]
[/TR]
[TR]
[TD="width: 64"]Pig[/TD]
[TD="width: 64"]80[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Column 1[/TD]
[TD="width: 64"]Column 2[/TD]
[TD="width: 64"]Outcome[/TD]
[/TR]
[TR]
[TD="width: 64"]Chicken[/TD]
[TD="width: 64"]-[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="width: 64"]Duck[/TD]
[TD="width: 64"]-[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="width: 64"]Dog[/TD]
[TD="width: 64"]-[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="width: 64"]Butterfly[/TD]
[TD="width: 64"]Snake[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="width: 64"]-[/TD]
[TD="width: 64"]Pig[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD="width: 64"]-[/TD]
[TD="width: 64"]Ant[/TD]
[TD="align: right"]70[/TD]
[/TR]
</tbody>[/TABLE]


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="class: grid, width: 437"]
<tbody>[TR]
[TD="colspan: 2"]Table[/TD]
[TD][/TD]
[TD][/TD]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Outcome[/TD]
[/TR]
[TR]
[TD]Chicken[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]Chicken[/TD]
[TD]-[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Duck[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD]Duck[/TD]
[TD]-[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD]Dog[/TD]
[TD]-[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Snake[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD]Butterfly[/TD]
[TD]Snake[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Bear[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD]-[/TD]
[TD]Pig[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]Elephant[/TD]
[TD]60[/TD]
[TD][/TD]
[TD][/TD]
[TD]-[/TD]
[TD]Ant[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD]Ant[/TD]
[TD]70[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pig[/TD]
[TD]80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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