Lookup data problem

[Eti32]

hello all,

I hope you can help me with this,
I need to find a way how to lookup a value that is changing.
i will now explain, according to a "question Id" value i need to find it's "question type"
for example
i have: these question types: GMAT, MCAT, MULTIPULE
and these id's: Num_gx, num_ra, rag_d

now its would have been simple if the data came as the id presented, but the data in the report comes with numbers attached to the ID
so each question has it's uniqe id e.g.- num_gx05, num_gx89, num_ra0009, num_ra34, rag_d4 etc..

now if num_gx= MCAT, num_ra=GMAT, rag_d=MULTIPULE

what function can i use for it to bring me the question type each tume i would insert an id?

i tried it with vlookup rang 0 and rang 1, with 1 i brought me wrong answers and with 0 it gave me N/A since each id has a unique number attached to it, there is no way of creating data base with all the number combinations, so what can i do to find the question type according to only the begining of the ID excluding the number that follows?

thanks

[Tony Valko]

Try this...

Create the table in the range E1:F3.

Data Range

	A	B	C	D	E	F
1	num_gx05	GMAT	------	------	Num_gx	GMAT
2	num_gx89	GMAT			num_ra	MCAT
3	num_ra0009	MCAT			rag_d	MULTIPULE
4	num_ra34	MCAT
5	rag_d4	MULTIPULE

Then, this formula entered in B1 and copied down:

=LOOKUP(1E100,SEARCH(E$1:E$3,A1),F$1:F$3)

[Eti32]

wow Tony, thank you!
can you explain what did you exactly do here?
what does the 1e100 stand for?
and basically if you can explain how did you built the function i would be grateful

[Tony Valko]

1E100 is an individual-bound non-sense.

I see you met the angry old man! If you don't do things his way he gets mad.

I also noticed that he did not explain how that specific formula works.

Rather, he wants you to use:

=LOOKUP(9.99999999999999E+307,SEARCH(E$1:E$3,A1),F$1:F$3)



I'll explain very clearly how the formula works...

We want to check a cells contents to see if it contains one of several strings of characters:

E1 = Num_gx
E2 = num_ra
E3 = rag_d

To do this we use the SEARCH function.

SEARCH(E$1:E$3,A1)

Searching cell A1 for each of the strings.

If the string is found SEARCH returns the character number where the string is located. For example:

A1 = I like to play golf

SEARCH("play",A1) returns 11 because the string "play" was found and it located starting at character 11 within the cell.

Also of note, SEARCH is case insensitive. In the SEARCH function, "PLAY" and "play" are the same.

If the string is not found within the cell then SEARCH returns the #VALUE! error.

A1 = num_gx05

SEARCH(E1,A1) = 1
SEARCH(E2,A1) = #VALUE!
SEARCH(E3,A1) = #VALUE!

This array is passed to the LOOKUP function:

LOOKUP(1E100,{1;#VALUE!;#VALUE!},...

In this application the way LOOKUP works is that if the lookup value 1E100 is greater than any other number in the generated array then LOOKUP will find/match the *LAST* number in the array.

The lookup value 1E100 is scientific notation for the very large number 1 followed by 100 zeros. That's one gigantic number!

A cell can contain a max of 32,767 characters. So, that means the largest number that SEARCH can return is 32,767.

Generally, we don't know how long the strings might be in a cell so we arbitrarily use gigantic lookup values to assure that the LOOKUP function returns the correct result. I like to use 1E100 because it's easy to remember, easy to type. and is guaranteed to work correctly.

In reality the lookup value simply needs to be greater than any other number in the array. PERIOD!

After seeing this again it looks like these ID codes will ALWAYS be the left-most characters in the cell. If that's the case then the max number that SEARCH will return is 1. And, if that's the case then we can use an even smaller (read: more realistic) lookup value, like 2!

Ok, so we have:

LOOKUP(1E100,{1;#VALUE!;#VALUE!},...

The *LAST* number in the array is also the only number in the array.

So, we have:

LOOKUP(1E100,{1;#VALUE!;#VALUE!},F1:F3)

In plain English:

Return the value in F1:F3 that corresponds to the *LAST* number in the array {1;#VALUE!;#VALUE!}

1; F1
#VALUE!; F2
#VALUE!; F3

So:

=LOOKUP(1E100,SEARCH(E$1:E$3,A1),F$1:F$3)

Returns: GMAT

As noted earlier, if the ID code is ALWAYS the left-most string of characters in the cell then the largest number SEARCH will return is 1 and in that case we can simply use 2 as the lookup value:

=LOOKUP(2,SEARCH(E$1:E$3,A1),F$1:F$3)

Take your pick:

=LOOKUP(2,SEARCH(E$1:E$3,A1),F$1:F$3)
=LOOKUP(9.99999999999999E+307,SEARCH(E$1:E$3,A1),F$1:F$3)

I know which one I'd use.

How many 9s is that? 1, 2, 3, 4, ............

Hopefully, the angry old man will see this and it will cause his head to explode!

[Eti32]

wow, that is amazing, thank you so much!!

[Tony Valko]

You're welcome. Thanks for the feedback!