@MF
I assume you received the example I sent to you OK.
@R4+Z
Yes, you can use VLOOKUP, However for business applications it is less robust than the above approach.
Try this example:
In cells A1:A4 enter: 1,2,3,4
In cells B1:B4 enter: a,b,c,d
In cells C1:C4 enter: e,f,g,h
In Cell A5 enter: Look up Value
In Cell A6 enter: 3
In Cell A7 enter: Result
In Cell A8 enter: =VLOOKUP(A6,A1:C4,3)
Cell A8 evalutes to: g
Circumstances change, so you need to add another column.
Select column B, then click Insert, Columns
You will see that cell A8 is now =VLOOKUP(A6,A1:D4,3) and evaluates to c
This occurs because the 3 in =VLOOKUP(A6,A1:D4,3) is an absolute reference to the columns in A1:D4 and does not get updated.
It is an insidious error and easily missed. Why risk it?
EG