Conditional VLOOKUP ??

[Wolfreton1989]

90 92 90 95 A
80 84 78 80 B
70 73 69 72 C
60 65 60 62 D

1 2 3 4

Hope this makes sense - I have a spreadsheet that uses a COUNTIF function to report a number (1 to 4 - bottom row). If, for example, the COUNTIF value is 3, I want to use the data from column 3 as a VLOOKUP to report the letter from the right hand column. If the COUNTIF number is 1, I want to use the data in column 1 as my VLOOKUP to report the letter from the far column.

I have six columns of numbers in total in my spreadsheet, plus the 7th column of reportable grades.

Any ideas?
Cheers
PK

 

[Wolfreton1989]

I realise I have posted my columns in descending order - but hopefully you can catch my drift!

 

[pjmorris]

Hi PK,

You need to define the range in the VLOOKUP function using the offset command: =VLOOKUP(H1, OFFSET(A1, 0, C6-1, 4, 6-C6) ,6-C6, FALSE) the numbers here refer to the layout you have above with the figure to be looked up in Cell H1. You'll need to adapt the elements of the Offset statement in particular:

Disecting OFFSET(A1, 0, C6-1, 4, 6-C6):
A1 should be changed to the address of the top left cell of your data,
0 is the row offset from that address, hence 0.
C6-1 is the column offset from the reference address, A1 in this example. Note that if C6 must not exceed the number of source columns you have.
4 is the number of rows of data.
6-C6 indicates the number of columns the data should span, thus if column 2 is the column in which Vlookup must search then this term must return the number of columns from there to the column of letters in this case 4 columns (B to E). Note also that the same term exists in the VLOOKUP function as it needs to calculate where the list of letters exists.

Not sure I've explained it properly, but hopefully this will give you some help.

All the best.