Lookup more than one table,depending on user input

[interested]

Hi,
can someone assist me...

i want to be able to select a "version" that looks at the fault code table, and selects the correct code description for that version.
At the moment i am using 2 columns to do what i want, but what is a better way. I may need to expand on the version codes, so this needs to be included also in the answer.

im using vlookup at the moment, but if the formula is changed, can someone explain

please see attachement...

Many thanks

[ratcat]

G'day

Basically you need to make your 'table_array' in the vlookup formula dynamic.

With your tables ranges already named I added a helper columns to make the selection name in 'Version' more user friendly.

See attached.

[interested]

Thanks mate !!! so does the indirect funtion make the lookup dynamic?

i've never used that function before

this will help me a lot

[stunn]

Another method, without the helper columns, but reliant on your tables following the order of the version list:

Please Login or Register  to view this content.

[interested]

Ratcat: now i copy the formula into my workbook i cant get it to see "code". i have created it in the list but cant seem to get it to work.

Stunn : thanks for your contribution, but my excel aint that good to understand your formula,

i have attached the real document, so is it possible you both can modify it to work, then i can follow the formula

Thank you both again......

[stunn]

Put this in K2 and copy downward:

Please Login or Register  to view this content.

[interested]

Thanks Stunn....it works for me, but i dont know how??? i just added another failure column in too and its perfect. thanks

---------- Post added at 02:19 PM ---------- Previous post was at 02:08 PM ----------

Thanks Stunn....it works for me, but i dont know how??? i just added another failure column in too and its perfect. thanks

[stunn]

You're welcome. Think of it as a normal VLOOKUP, but the OFFSET does exactly what it says, it offsets the reference by a given amount of rows or columns (you can also change height and width but that isn't necessary here). So, for instance, the original reference given to OFFSET is product_fail_codes (your first table). If $F2 is blank, the IF returns 0 because we don't want any offset, instead we want it to stick to the original reference, however, if there is a value in $F2 we look it up in the version table. In the case of Iguana, the MATCH returns 2 because Iguana is in the second cell within the version table. Now we need to translate that to an offset that matches the positions of your tables. Since the first column of each table is 3 columns away from the previous one we need to multiply by 3 and then subtract 3 to get the correct position, it could equally have been (MATCH(...)-1)*3

[ratcat]

Ratcat: now i copy the formula into my workbook i cant get it to see "code". i have created it in the list but cant seem to get it to work............

It appears that this thread is solved. If I'm right please mark the thread accordingly.

But to answer the "i cant get it to see "code" question.

What I have done is named the table_array in the vlookup.

Click in the link for some background info. http://www.contextures.com/xlNames01.html

And thanks to stunn for there contribution

Cheers