Matching values in different sheets and rows

[AA Arens]

To fill in a value V, with the restriction that the stock number
(10-40) should have the same value as in Sheets B, C, ..., I use in a
cell right of the stock numbers in Sheet A the folllowing code:

=IF(ISNUMBER(MATCH(C7,'SheetB'!A:A,0)),'SheetB'!C1,IF(ISNUMBER(MATCH
etc...

But the values in sheets B, C etc are not filled in in the same order
as Sheet A.

How to transfer that the value V is from a certain row?


Sheet A

10 V
20 V
30 V
40 V
...

Sheet B

40 XX
30 ZZ
...

V should be filled in with value where 10 sheet A = 10 sheet B etc.
So:

30 ZZ
40 XX

See also:

http://groups-beta.google.com/group...a8f?lnk=st&q=aa+arens&rnum=7#dbe8e71e03097a8f

Bart
Excel 2003

 

[Pete_UK]

In your formula you are asking to return from SheetB!C1 if there is a
match - surely you want to return from the row that the match occurs,
using an INDEX/MATCH combination, or a VLOOKUP like this:

=IF(ISNUMBER(MATCH(C7,'SheetB'!A:A,0)),VLOOKUP(C7,'SheetB'!A:C,3,0),IF(ISNUMBER(MATCH
etc

The formula can be copied down. Strictly speaking, apostrophes around
the sheetname are only needed if you have spaces in the name.

Hope this helps.

Pete