VLOOKUP "restart"

[Excelllllllllllllllllllll]

[TABLE="width: 500"]
<tbody>[TR]
[TD]0[/TD]
[TD]A[/TD]
[TD]=VLOOKUP($D$1,$A$25:$B$25,2)[/TD]
[TD]26[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]B[/TD]
[TD]=VLOOKUP($D$1,$A$25:$B$25,2)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]C[/TD]
[TD]=VLOOKUP($D$1,$A$25:$B$25,2)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]D[/TD]
[TD]=VLOOKUP($D$1,$A$25:$B$25,2)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Column A has numbers from 0 to 25.
Column B has the letters of the alphabet.
Column C should only have the letter that is looked up from Cell D1.

I've ran into the problem where if I lookup a number higher than 25 I get an error - obviously.

I was wondering if there's a workaround where VLOOKUP will reset after 25 so if, for example the number 26 is looked up it goes back to the letter A. If 27 was looked up it shows B. Etc.

The lookup value is unlimited so the only way I can think of to get around this is to copy-paste A:B all the way to the end of the worksheet (1048576) and even then I won't be able to look for the next number.

Please help.

 

[Excelllllllllllllllllllll]

After some playing around I found out that VLOOKUP has a limitation of 255 characters being looked up so that's another problem I have to overcome, but I have no experience with INDEX or MATCH, though I have seen others use them to great effect in similar situations.

 

[Toadstool]

I'm not sure I understand your requirement but you can use MOD to limit the range of D1
=(MOD($D$1,26))
so if D1=0 you'll get 0, if 25 you'll get 25 but if 30 you'll get 4.

I was confused that in column C you only search row 25 and don't specify a range_lookup value so I'll assume typos and row should be relative, VLOOKUP should use range_lookup FALSE for an exact match.

So copying column C down from row 1
=VLOOKUP((MOD($D$1,26)),$A1:$B1,2,FALSE)
and if you don't want all the #N/A entries then
=IFERROR(VLOOKUP((MOD($E$1,26)),$A1:$B1,2,FALSE),"")

Is that what you were looking for?

 

[Excelllllllllllllllllllll]

This is brilliant, thank you!

On the offchance that I ever need it, is there a way to subvert the VLOOKUP limitation of 250 characters?
Say, if I wanted to VLOOKUP like the above example, but I had numbers in A1:A1048576 like a monster.

Cheers!

 

[Toadstool]

My mistake, I used the wrong reference on the last example and it should be:
=IFERROR(VLOOKUP((MOD($D$1,26)),$A1:$B1,2,FALSE),"")

As I said it's not clear what your objective is but the formula provided cane be put in C1 and copied down every row where there's data in cells A and B:

• It takes the number you've put in D1 and restricts it to 0 to 25, so 0=0, 1=1, 25=25 but 26=0, 27=1, 28=2, 333=21, 520=0 and so on.
• It looks down column A for a match by that restricted value. If it finds a match it displays what is in that row in column B. If it doesn't find a match it displays nothing ("").

The VLOOKUP restriction means it returns a #VALUE ! error if a cell used to search with or search into exceeds 255 characters length. In this case D1 is never larger than 2 digits and each A cell is between 1 and 7 digits, each B cell is one character, so no error will arise.