Not sure if vlookup correct for problem

[Martin.R]

Not sure if VLOOKUP is what I need, tried and it did not work very well.

I have 2 sheets, they both contain 2 colums.
Sim & Modem, both these consist of a set of numbers.

I need to compare a master sheet against another sheet, like this:

Master
Sim Modem
123 345
355 233

Other
Sim Modem
123 345
355 342

I need to try and compare the sim number matchs the modem number next to it on the other sheet using the master as the source, and in some way mark it what matches, there are about 300 rows.

Thanks in advance for any help.

[jasonanthony]

I think you can use a concatonation, and then do a vlookup off of that

[ratcat]

G'day,

Try this,

On the other sheet insert a new column, so the Sim info is in col B and the Modem info is in col C.

In cell A2 insert formula

=B2&C2

then copy down.

On the master sheet, if Sim info is col A and the Modem info is col B.

In cell C2 insert formula

Please Login or Register  to view this content.

OTHER SHEET. Please insert the correct name sheet reference according to your workbook

Hope that helps

[Martin.R]

Many thanks ratcat, working just right.

I guess I did not think of the obvious of adding the 2 together and doing a simple vlookup on the single number that way >.<

Thanks again.

[Martin.R]

Hmm ok struggling again

I now need to do add the following:
Instead printing "Match" I need to display the data from the other sheet on colum 5.

So I though this would work

Please Login or Register  to view this content.

But just get #REF

Thanks again for any more help

[ratcat]

Hmm ok struggling again

I now need to do add the following:
Instead printing "Match" I need to display the data from the other sheet on colum 5.

So I though this would work

Please Login or Register  to view this content.

But just get #REF

Thanks again for any more help

Ok, you need to change your table array in the formula.

)),"No Match",VLOOKUP(A2&B2,OTHER SHEET$A$2:$A$1000,5,FALSE)

From $A$2:$A$1000

to $A$2:$E$1000

Because from Col A to Col E is 5 step across.

eg A=1,B=2,C=3,D=4,E=5,F=6,G=7, etc, etc,

But in saying that if your table range starts at col D.

Col D becomes 1, col E=2 etc, etc.

Cheers