Formula Help - Lookup, if, iserror??

  • Thread starter Thread starter Ash
  • Start date Start date
A

Ash

I have data in two worksheets that I would like to compare/link. To keep it
simple, suppose Wks 1 has 2 columns of data and Wks 2 has 3 columns of data.

Columns A and B in both spreadsheets have like data. And for every row that
exists in Wks 1, there is an match (with respect to columns A and B) in Wks 2.

Wks 1: Wks 2:
A B A B C
Story 2 Story 1 Red
Story 1 Story 2 Blue
Apple 3 Candy 1 Red
Candy 1 Apple 3 Red

I would like to populate Column C in Wks 1, with the corresponding data in
Column C from Wks 2.
So if, column A matches, then if column B matches, then C. If at any point
it doesn't match, it should continue looking for the matching A/B combination
to get C.

I hope this makes sense. I think I'm really having a blonde moment.

Thanks!
Ash
 
Hi Ash,

Use a helper column
Concatenate A and B in that column and use the concatenation of the search
arguments in VLOOKUP
 
Copy this into C1 of Sheet1 (assuming no header row)

=INDEX(Sheet2!$C$1:$C$10,MATCH(1,(Sheet2!$A$1:$A$10=A1)*(Sheet2!$B$1:$B$10=B1),0))

[Adjust 10 to the last row in your set]

then press CTRL-SHIFT-ENTER and copy down till you want

If sheets are in different files then you will have to add the filename
before the sheetname in []
 
for every row that exists in Wks 1, there is an match... in Wks 2.

OK, then you shouldn't have to be concerned with errors in not finding
matching.

Try this array formula** :

=INDEX(Sheet2!C$1:C$4,MATCH(1,(Sheet2!A$1:A$4=A1)*(Sheet2!B$1:B$4=B1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
Here is the formula I used:

=INDEX('Adding Funding Source'!$M$1:$M$337,MATCH(1,('Adding Funding
Source'!$A$1:$A$337=B1)*('Adding Funding Source'!$N$1:$N$337=M1),0))

Here are the columns that Match:
Column B in Wks 1 matches Column A in Wks 2 (Adding Funding Source)
Column M in Wks 1 matches Column N in Wks 2 (Adding Funding Source)
Column M in Wks 2 is the one that contains the data I would like to display
in Wks 1.

I entered the formula above based on the formula you provided, and after I
pressed Clt-Shift-Enter, I got 0. The data that should have displayed was a
text statement.

Did I do something wrong?

Thanks.

Sheeloo said:
Copy this into C1 of Sheet1 (assuming no header row)

=INDEX(Sheet2!$C$1:$C$10,MATCH(1,(Sheet2!$A$1:$A$10=A1)*(Sheet2!$B$1:$B$10=B1),0))

[Adjust 10 to the last row in your set]

then press CTRL-SHIFT-ENTER and copy down till you want

If sheets are in different files then you will have to add the filename
before the sheetname in []


Ash said:
I have data in two worksheets that I would like to compare/link. To keep it
simple, suppose Wks 1 has 2 columns of data and Wks 2 has 3 columns of data.

Columns A and B in both spreadsheets have like data. And for every row that
exists in Wks 1, there is an match (with respect to columns A and B) in Wks 2.

Wks 1: Wks 2:
A B A B C
Story 2 Story 1 Red
Story 1 Story 2 Blue
Apple 3 Candy 1 Red
Candy 1 Apple 3 Red

I would like to populate Column C in Wks 1, with the corresponding data in
Column C from Wks 2.
So if, column A matches, then if column B matches, then C. If at any point
it doesn't match, it should continue looking for the matching A/B combination
to get C.

I hope this makes sense. I think I'm really having a blonde moment.

Thanks!
Ash
Click to expand...
Click to expand...
 
Sheloo above gave the same recommendation. I made an attempt at the formula,
but it did not work. Please feel free to see my response to the post above.

Thank you kindly for a response.
 
Niek,

This may be the simplest/easiest solution for me to try. Thank you kindly
for the response.
 
This returns the value in column M for the first match in columns A and N.

My bet is that you didn't notice that you had a match higher in your list.

If you try this array formula:

=MATCH(1,('Adding Funding Source'!$A$1:$A$337=B1)
*('Adding Funding Source'!$N$1:$N$337=M1),0)

(ctrl-shift-enter, still!)

It'll show you the first row in that "adding funding source" worksheet that has
a match in colunn A and N.
Here is the formula I used:

=INDEX('Adding Funding Source'!$M$1:$M$337,MATCH(1,('Adding Funding
Source'!$A$1:$A$337=B1)*('Adding Funding Source'!$N$1:$N$337=M1),0))

Here are the columns that Match:
Column B in Wks 1 matches Column A in Wks 2 (Adding Funding Source)
Column M in Wks 1 matches Column N in Wks 2 (Adding Funding Source)
Column M in Wks 2 is the one that contains the data I would like to display
in Wks 1.

I entered the formula above based on the formula you provided, and after I
pressed Clt-Shift-Enter, I got 0. The data that should have displayed was a
text statement.

Did I do something wrong?

Thanks.

Sheeloo said:
Copy this into C1 of Sheet1 (assuming no header row)

=INDEX(Sheet2!$C$1:$C$10,MATCH(1,(Sheet2!$A$1:$A$10=A1)*(Sheet2!$B$1:$B$10=B1),0))

[Adjust 10 to the last row in your set]

then press CTRL-SHIFT-ENTER and copy down till you want

If sheets are in different files then you will have to add the filename
before the sheetname in []


Ash said:
I have data in two worksheets that I would like to compare/link. To keep it
simple, suppose Wks 1 has 2 columns of data and Wks 2 has 3 columns of data.

Columns A and B in both spreadsheets have like data. And for every row that
exists in Wks 1, there is an match (with respect to columns A and B) in Wks 2.

Wks 1: Wks 2:
A B A B C
Story 2 Story 1 Red
Story 1 Story 2 Blue
Apple 3 Candy 1 Red
Candy 1 Apple 3 Red

I would like to populate Column C in Wks 1, with the corresponding data in
Column C from Wks 2.
So if, column A matches, then if column B matches, then C. If at any point
it doesn't match, it should continue looking for the matching A/B combination
to get C.

I hope this makes sense. I think I'm really having a blonde moment.

Thanks!
Ash
Click to expand...
Click to expand...
Click to expand...
 
You got it right.

See Dave's post for a possible reason.

You will also get 0 if there is no match.

To test add a row with the values you are testing with just to be sure. I
always copy the cells to make sure everything is same..


Ash said:
Here is the formula I used:

=INDEX('Adding Funding Source'!$M$1:$M$337,MATCH(1,('Adding Funding
Source'!$A$1:$A$337=B1)*('Adding Funding Source'!$N$1:$N$337=M1),0))

Here are the columns that Match:
Column B in Wks 1 matches Column A in Wks 2 (Adding Funding Source)
Column M in Wks 1 matches Column N in Wks 2 (Adding Funding Source)
Column M in Wks 2 is the one that contains the data I would like to display
in Wks 1.

I entered the formula above based on the formula you provided, and after I
pressed Clt-Shift-Enter, I got 0. The data that should have displayed was a
text statement.

Did I do something wrong?

Thanks.

Sheeloo said:
Copy this into C1 of Sheet1 (assuming no header row)

=INDEX(Sheet2!$C$1:$C$10,MATCH(1,(Sheet2!$A$1:$A$10=A1)*(Sheet2!$B$1:$B$10=B1),0))

[Adjust 10 to the last row in your set]

then press CTRL-SHIFT-ENTER and copy down till you want

If sheets are in different files then you will have to add the filename
before the sheetname in []


Ash said:
I have data in two worksheets that I would like to compare/link. To keep it
simple, suppose Wks 1 has 2 columns of data and Wks 2 has 3 columns of data.

Columns A and B in both spreadsheets have like data. And for every row that
exists in Wks 1, there is an match (with respect to columns A and B) in Wks 2.

Wks 1: Wks 2:
A B A B C
Story 2 Story 1 Red
Story 1 Story 2 Blue
Apple 3 Candy 1 Red
Candy 1 Apple 3 Red

I would like to populate Column C in Wks 1, with the corresponding data in
Column C from Wks 2.
So if, column A matches, then if column B matches, then C. If at any point
it doesn't match, it should continue looking for the matching A/B combination
to get C.

I hope this makes sense. I think I'm really having a blonde moment.

Thanks!
Ash
Click to expand...
Click to expand...
Click to expand...
 
You will also get 0 if there is no match.

No, you'll get a result of #N/A if there is no match.


--
Biff
Microsoft Excel MVP


Sheeloo said:
You got it right.

See Dave's post for a possible reason.

You will also get 0 if there is no match.

To test add a row with the values you are testing with just to be sure. I
always copy the cells to make sure everything is same..


Ash said:
Here is the formula I used:

=INDEX('Adding Funding Source'!$M$1:$M$337,MATCH(1,('Adding Funding
Source'!$A$1:$A$337=B1)*('Adding Funding Source'!$N$1:$N$337=M1),0))

Here are the columns that Match:
Column B in Wks 1 matches Column A in Wks 2 (Adding Funding Source)
Column M in Wks 1 matches Column N in Wks 2 (Adding Funding Source)
Column M in Wks 2 is the one that contains the data I would like to
display
in Wks 1.

I entered the formula above based on the formula you provided, and after
I
pressed Clt-Shift-Enter, I got 0. The data that should have displayed
was a
text statement.

Did I do something wrong?

Thanks.

Sheeloo said:
Copy this into C1 of Sheet1 (assuming no header row)

=INDEX(Sheet2!$C$1:$C$10,MATCH(1,(Sheet2!$A$1:$A$10=A1)*(Sheet2!$B$1:$B$10=B1),0))

[Adjust 10 to the last row in your set]

then press CTRL-SHIFT-ENTER and copy down till you want

If sheets are in different files then you will have to add the filename
before the sheetname in []


:

I have data in two worksheets that I would like to compare/link. To
keep it
simple, suppose Wks 1 has 2 columns of data and Wks 2 has 3 columns
of data.

Columns A and B in both spreadsheets have like data. And for every
row that
exists in Wks 1, there is an match (with respect to columns A and B)
in Wks 2.

Wks 1: Wks 2:
A B A B C
Story 2 Story 1 Red
Story 1 Story 2 Blue
Apple 3 Candy 1 Red
Candy 1 Apple 3 Red

I would like to populate Column C in Wks 1, with the corresponding
data in
Column C from Wks 2.
So if, column A matches, then if column B matches, then C. If at any
point
it doesn't match, it should continue looking for the matching A/B
combination
to get C.

I hope this makes sense. I think I'm really having a blonde moment.

Thanks!
Ash
Click to expand...
Click to expand...
Click to expand...
 
Back
Top