Vlookup from one sheet to the other

[MagicMan]

Hi Guys,

I'm not sure if this is in the right area, but thought I'd give it a shot,

I've attached a spreadsheet with three tabs. Basically, what I'd like is for each tab to look at the previous tab and match the data in column B. Then if there's a match look at whether the number in column B is greater, the same or lesser. If it's less I want a "2" inserting in column C, if it's more, a "1" and if it's the same a "3". If there's no match then insert a "4".

In my example, I want "Previous Sheet" to look at "Original Sheet" and "New Sheet" to look at "Previous Sheet" (Further sheets will be added as time goes on). So I'm guessing that the solution will have to reside within the sheet.

Any ideas?

Many Thanks.

[AliGW]

You mention numbers in column B, but I see only letters. Can you provide a sample workbook with more realistic data?

[JohnTopley]

I used a "helper" column E in "Previous" with the following formula ..

in E3 and copy down

=IFERROR(INDEX('Original Sheet'!$A$3:$A$8,MATCH('Previous Sheet'!B3,'Original Sheet'!$B$3:$B$8,0)),0)

No match returns 0

In C3 and copy down

=IF(E3=0,4,IF(A3<E3,1,IF(A3=E3,3,IF(A3>E3,2))))

Repeat for "Next" vs "Previous"

If you have a standardised naming convention for your sheets it might be possible to generate a "generic" set of formulae