Look up value in Multiple ranges

[somnath6309]

Please find enclosed herewith an excel file. In sheet1 there are three data tables (all are pink colored). Below the data tables there is anther table like this (yellow colored portion is the formula portion):
Coupon no. Vendor
55555 A
130005 B
330009 C
here , the vendors are being looked up with respect to coupon nos. the following formula has been used :
LOOKUP(REPT("z",99),CHOOSE({1,2,3},VLOOKUP(A13,$A$2:$C$5,3,1),VLOOKUP(A13,$E$2:$G$5,3,1),VLOOKUP(A13,$I$2:$K$5,3,1)))

clearly Lookup, choose, vlookup formulas have been used. But I have not understood why the portion : "Rept("z",99)" [vide red colored portion] has been used as the lookupvalue argument of the Lookup Formula ? clearly the portion will repeat the "z" letter for 99 times but what will be achieved by that ?
looking for a suitable reply.
Regards
Somnath6309

 

[Hui]

Somnath6309

The use of Rept("z",99)is simply making a string that should be larger than the largest string you are likely to use in your data

Hence the function is looking for the last text entry

If you know your data you may be able to use a simple "ZZZZ" instead of the Rept() function, Just ensure it is larger than the largest text string likely in that field

In you example you could simply use "D" as it is greater than A, B or C

Remember than when comparing text Excel looks at the ascii values for the text
so z=Char(122), Z = char(90)
z is greater than Z