Compair two pair of cells: function usage

[and_woox]

Hello!

I'm looking for a way to do the following:

-Compare a pair of cells on sheet A to another pair of cells on sheet B.
-If the pairs match (only one match will be possible), return the value from the column on sheet B corresponding to that matched pair.



Can somebody help me?

 

[ETAF]

index(sheetb!$C$2:$C$1000, match(A3&B3,sheetb!$A$2:$A$1000&sheetb!$B$2:$B$1000,0))
BUt with numbers joined like that - you can get things matching wrongly
Like

1 & 12
is the same as
11&2
for example

=LOOKUP(2,1/(sheetb!$A$2:$A$1000=A3)/(sheetb!$B$2:$B$1000=b3),(sheetb!$C$2:$C$1000))

rather than an image load a sample sheet

Also version of excel my help

I'm assuming Sheetb - is columns A , B & C

 

[and_woox]

Thanks for the reply, unfortunately it didn't work.
I uploaded the original file.
What I'm trying to do is:

-In sheet REDE COLETORA DE ESGOTO match the pair of cells on column B and C with the corresponding pair in sheet APOIO.
-In sheet REDE COLETORA DE ESGOTO column D return the value from sheet APOIO column D that corresponds to the matched pair.

Can anyone give it a go on the original spreadsheet?

 

[ETAF]

a few things make it difficult - although both formulas work to some extent

=INDEX(APOIO!$D$2:$D$1000,MATCH(B13&C13,APOIO!$B$2:$B$1000&APOIO!$C$2:$C$1000,0))

you have merged cells - rows - through out ...

Alss you are looking for Text and Numbers
for example
02 is text and so will not find 2

=LOOKUP(2,1/(APOIO!$B$2:$B$1000=B13*1)/(APOIO!$C$2:$C$1000=C13*1),(APOIO!$D$2:$D$1000))

this will find most values - but will need to have an IF - where the criteria is not 02 - but has a letter in

but before i spend any more time on it

can the original be cleaned up without merged cells ???

also various error checking needed - for some of the different format - TEXT to numbers

 

[bosco_yip]

Hi, @ETAF

1] Your formula: =IF(B13="","", LOOKUP(2,1/(APOIO!$B$2:$B$1000=B13*1)/(APOIO!$C$2:$C$1000=C13*1),(APOIO!$D$2:$D$1000)))

It is a good formula for 2 criteria Lookup.

However

2] I just suggest a better, shorter and faster format to use Lookup function as similar to yours.

=IF(B13="","",LOOKUP(9.9E+307,APOIO!$D$2:$D$1000/(APOIO!$B$2:$B$1000=--B13)/(APOIO!$C$2:$C$1000=--C13)))

3] Explanation

3.1] Your Lookup formula Lookup method is using: "Vector form."

Syntax: LOOKUP(lookup_value, lookup_vector, [result_vector])

3.2] My Lookup formula Lookup method is using: "Array form."

Syntax: LOOKUP(lookup_value, array)

The lookup_value, I used 9.9E+307 is a BigNum (Please Google of this word)

4] The Lookup formula searching way

4.1] Lookup Vector form: using linear way (Top-down searching way)

4.2] Lookup Array form: using Binary way (1/2>>1/4>>...... searching way, Please Google Binary way)****a very fast search wann

5] Conclusion

Lookup "Array form" is faster than Lookup "Vector form" a very long distance.

So,

Array form > Vector form

6] Remark

6.1] If the [result_vector] is Number, you need to use:

LOOKUP(9.9E+307,[result_vector]/(Condition 1)/(Condition 2))

6.2] If the [result_vector] is Text, you need to use:

LOOKUP(2,1/(Condition 1)/(Condition 2),[result_vector])

Regards
Bosco

 

[pecoflyer]

Cross posted at
https://www.excelforum.com/excel-fo...pair-of-cells-function-usage.html#post5785712

Compair two pair of cells: function usage

Compair two pair of cells: function usage Excel

www.msofficeforums.com

Compair two pair of cells: function usage

Hello! I'm looking for a way to do the following: -Compare a pair of cells on sheet A to another pair of cells on sheet B. -If the pairs match (only one match will be possible), return the value from the column on sheet B corresponding to that matched pair. Can somebody help me?

www.mrexcel.com

Compair two pair of cells: function usage

Hello!I'm looking for a way to do the following:-Compare a pair of cells on sheet A to another pair of cells on sheet B.-If the pairs match (only one match..

techcommunity.microsoft.com

 

[vletm]

and_woox
As You've read before Your the 1st posting

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.