VLOOKUP 256 Character Limit

[jwehrly2000]

Hello everybody, I have a multiple choice testing bank with a random question generator. I am working on getting the answers to populate on the master test version but I think the amount of characters is causing some #VALUE errors.

=VLOOKUP(B13,'3P051 VOL 1 BANK'!B:C,2,FALSE)

B13 = Cell to search for

'3PO51 VOL 1 BANK'!B:C = sheet name and cells to search


Is there a way to convert this formula to work with the INDEX MATCH function?

This data is in column B and the formula returns the adjacent cell (Column C) values.

Thanks for any help you can provide.

 

[FormR]

Hi, you can try like this:

=INDEX('3P051 VOL 1 BANK'!$C$1:$C$1000,MATCH(TRUE,INDEX('3P051 VOL 1 BANK'!$B$1:$B$1000=B13,0),0))

Note: for efficiency reasons you should avoid using full column references - either use sensible maximums or look into using dynamic named ranges or using tables and structured references.