# Conditional VLOOKUP ??

#### Wolfreton1989

##### New Member
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

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### Wolfreton1989

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

#### pjmorris

##### Well-known Member
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.

Replies
5
Views
335
Replies
4
Views
191
Replies
0
Views
125
Replies
3
Views
162
Replies
5
Views
270

1,196,007
Messages
6,012,825
Members
441,731
Latest member
jonceramic

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back