Looking for IF with VLOOKUP statement with two conditions.

[AA Arens]

This is the formula I currently use to copy a value X from another document in cell A:

Cell A:
=IF(ISERROR(VLOOKUP($B3;fsr.csv!$C$2:$H$2000;3;FALSE));"";VLOOKUP($B3;fsr.csv!$C$2:$H$2000;3;FALSE))

Now, I want to add another condition: another field in the same row from the CSV file must match in other to add the value X in cell A.

Example:
values from three columns from CSV file:
10 15 20

Fill in the cell A the value "20" only if row with both 10 and 15 exist in the CSV.

Thank you for your help.

 

[Claus Busch]

Hi,

Am Wed, 20 Feb 2013 04:17:43 -0800 (PST) schrieb AA Arens:

Example:
values from three columns from CSV file:
10 15 20

Fill in the cell A the value "20" only if row with both 10 and 15 exist in the CSV.

try:
=INDEX(fsr.csv!$C$2:$C$2000,MATCH(10&" "&15,fsr.csv!$A$2:$A$2000&" "&fsr.csv!$B$2:$B$2000,0))
and enter the array formula with CTRL+Shift+Enter


Regards
Claus Busch

 

[AA Arens]

This is the formula I currently use to copy a value X from another document in cell A:



Cell A:

=IF(ISERROR(VLOOKUP($B3;fsr.csv!$C$2:$H$2000;3;FALSE));"";VLOOKUP($B3;fsr.csv!$C$2:$H$2000;3;FALSE))



Now, I want to add another condition: another field in the same row from the CSV file must match in other to add the value X in cell A.



Example:

values from three columns from CSV file:

10 15 20



Fill in the cell A the value "20" only if row with both 10 and 15 exist in the CSV.



Thank you for your help.

=INDEX(fsr.csv!$C$2:$h$2000,MATCH($B3&" "&$E3,fsr.csv!$C$2:$c$2000&" "&fsr.csv!$f$2:$f$2000,0))

I do get an error.

I made a a bit simplier.

Fill in value "changed" in the cell if the current value from the other cell in the same row is the same from the value in the CSV file, while a third cell in the same row differes from the respective cell in the CSV file.


CSV:

Cell A - B - C
10 54 67
11 76 44
5 100 32

Current sheet:
Cell A - B - C

54 67
76 88 "Changed"
100 32

 

[Claus Busch]

Hi,

Am Wed, 20 Feb 2013 05:22:47 -0800 (PST) schrieb AA Arens:

CSV:

Cell A - B - C
10 54 67
11 76 44
5 100 32

Current sheet:
Cell A - B - C

54 67
76 88 "Changed"
100 32

try:
=IF(ISERROR(MATCH($A1&" "&$B1,fsr.csv!$B$2:$B$2000&" "&fsr.csv!$C$2:$C$2000,0)),"Changed","")


Regards
Claus Busch

 

[AA Arens]

Hi,



Am Wed, 20 Feb 2013 05:22:47 -0800 (PST) schrieb AA Arens:












try:

=IF(ISERROR(MATCH($A1&" "&$B1,fsr.csv!$B$2:$B$2000&" "&fsr.csv!$C$2:$C$2000,0)),"Changed","")





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Claus, thank you for your reply. The formula always gives "changed" event though the respective values in the row are the same as the values in another row from the CSV.
I have removed the &" " (space), is that correct?

What I am looking for is a two formulas in two cells in the row of the XLS file:

One cell indicates that the first value differs in either of the sheet while the other values was equal, and in another cell an indication that the second value was different while the first value remained the same.

It is not possible that both has changed (then the combination cannot be found either)

CSV:

Array with sample values in the columns:

10 54 67
11 76 44
5 100 32

XLS:

10 33 67 "changed" "same"
11 76 88 "same" "changed"
5 100 32 "same" "same"

 

[Claus Busch]

Hi,

Am Thu, 21 Feb 2013 20:11:11 -0800 (PST) schrieb AA Arens:

I have removed the &" " (space), is that correct?

no, because then 33 & 67 is the same as 3 & 367

CSV:

Array with sample values in the columns:

10 54 67
11 76 44
5 100 32

XLS:

10 33 67 "changed" "same"
11 76 88 "same" "changed"
5 100 32 "same" "same"

I hope, I do understand you correctly and the numbers are in .csv and
..xls in the same row and same column. Then in xls D1:
=IF(B1=fsr.csv!B1,"same","changed")
and copy to E1 and down.


Regards
Claus Busch

 

[AA Arens]

Hi,



Am Thu, 21 Feb 2013 20:11:11 -0800 (PST) schrieb AA Arens:






no, because then 33 & 67 is the same as 3 & 367










I hope, I do understand you correctly and the numbers are in .csv and

.xls in the same row and same column. Then in xls D1:

=IF(B1=fsr.csv!B1,"same","changed")

and copy to E1 and down.





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

We need an array statement as the values can be in any row position in the CSV. The values in the CSV are in a different (but same) column as the XLS, but I can modify that myself from your formula.