Index and Match Formula

[Barbara Schneier]

I have two columns in worksheet Number 1 (1607 rows) as follows:

Current Value Proposed Value

0105.92.00 0105.94.00.00
0105.93.00 0105.94.00.00
0208.20.00 0208.90.25.00
0301.99.00 0301.94.00.00
0301.99.00 0301.95.00.00
0301.99.00 0301.99.01
0302.69.20 0302.67.00
0302.69.40 0302.69.50
0302.69.40 0302.68.00
0303.50.00 0303.51.00.00
0303.60.00 0303.52.00
0303.79.20 0303.79.00
0303.79.20 0303.61.00
0303.79.40 0303.79.00
0303.79.40 0303.62.00
0304.10.10 0304.19.00


I have 23 Columns in Worksheet Number 2 (42 Rows).

In this worksheet there are 2 columns as follows

Current Value Proposed Value

0105.92.00
0301.99.00
0302.69.40
0303.50.00
0303.79.20
2513.11.00
2513.19.00
2920.10.30
2920.10.40
2920.10.50
2921.12.00

I need to lookup and match the Current Value column in the 2 worksheets
and when there is a match, but no change in value in the Proposed Value
Column in the Worksheet 1 to put the words "No Change" in the Proposed
Value cell for that match in Worksheet 2.

But

If there is a change in Worksheet 1's Proposed Value for that match to
insert the Proposed Value in Worksheet 2's Proposed Value for that match.

I cannot fit all the pieces together. I tried an index and match
formula as follows:

=INDEX(B5:B1607,MATCH(D5,A5:A1607,0)), which seemed to work, but I do
not know how to include if no change in proposed value to insert "No
Change."

In testing the Index and match I copied the 2 columns in Worksheet 1 to
Worksheet 2. Do all columns need to be in the same worksheet?


I found the following example for the "No Change:

=IF(ISNA(VLOOKUP(5,A2:E7,2,FALSE)) = TRUE, "Employee not found",
VLOOKUP(5,A2:E7,2,FALSE)), but do not know who to write and incorporate
it with the index and match formula.

I would appreciate any help anyone can give me as I need to be able to
begin work on a project this coming Monday.

 

[Jason Lepack]

I can't wrap my head around this. If you match from worksheet 2 to
worksheet 1 then you want find only one match. 0301.99.00 occurs 3
times in sheet 1? Which proposed value should go in sheet 2?

Cheers,
Jason Lepack

 

[Barbara Schneier]

Hi Jason,

Sorry for the confusion. Worksheet 2's Current Value column will have
the same numbers listed multiple times, and each time it needs to be
matched to the same value listed in Worksheet 1's Current Value column.

The Proposed value has to be from worksheet 1 and put into worksheet 2's
Proposed Value column (as shown there are no values in worksheet 2).

Barbara

 

[Guest]

I'm as confused as Jason ... In your first post it sounds like there are 3
sheets involved:

"I have two columns in worksheet Number 1..."
"I have 23 columns in Worksheet Number 2..."
"In this worksheet there are 2 columns as follows..."

In the reply to Jason it sounds like you are only using 2 sheets. If there
are only two sheets involved, give us some ranges used on each for each group
of data.

I think you're fighting an uphill battle here; VLOOKUP, INDEX, MATCH all
stop when they find the match, so when you have same value in the area, it's
going to stop when it hits the first one.

Now - about that sheet with 23 columns -- is each column a continuation of
the list from the previous column, for a visual example:
A B
1 21 26
2 22 27
3 23 28
4 24 29
5 25 30
where 6 is picking up in 2nd column where 1st column left off instead of
just going on down the sheet?
***
***
In the meantime, I keep looking at the first formula you used and wonder why
something like
=IF(INDEX(B5:B1607,MATCH(D5,A5:A1607,0))=INDEX(A5:A1607,MATCH(D5,A5:A1607,0)),"No Change",INDEX(B5:B1607,MATCH(D5,A5:A1607,0)))
would not work? But I'm really not certain at all. Having a clearer
picture of "Who's on first?" would help a lot.

 

[Barbara Schneier]

I am sorry again for my confusing explanation, and will try to make it
clearer.

There are only 2 worksheets

In worksheet 1 there are 2 columns and 1607 rows as follows:

A Current B Proposed

1 21 21 same
2 22 24 new number
3 23 28 new number
4 24 29 new number
5 25 25 same

worksheet 2 ha 23 columns and 300 rows(but the 2 columns involved in the
lookup and match are:

D Current E Proposed (where the new number or No change will be
inserted)
1 21

2 22
3 23
4 24
5 25

I need for the lookup to match Worksheet 1's column A Current number to
Worksheet's 2 column D current number. When it finds a match and the
Proposed number in worksheet 1's column B Proposed is also the same
number to insert "No Change" in Worksheet's 2 E Proposed column, but If
the number is different to put that new number.

Per your comment on duplicate numbers:
I think you're fighting an uphill battle here; VLOOKUP, INDEX, MATCH all

stop when they find the match, so when you have same value in the

area, it's going to stop when it hits the first one.

In worksheet 1 Under column A Current there will be the some numbers
(not many)listed twice, maybe more, and different new numbers for each
under B Proposed. I know these will have to be reviewed and updated
manually.

I hope this gives you a clearer picture. If not, please let me know
what needs clarification. Thanks for your help.

Barbara