Getting Information off table into cells

[Heva]

Hi all,

I am trying to create a form on excel spreadsheet with a list of names display from L1 to L10 and other information placed accordingly from N1 to N10, O1 to O10 and on

e.g
L_____N_______O
Alex___2780X___BA2304
Ben___8990B___CP7443
Don___3351L___FP9800

so that when the cell D24 displays the name, say Ben, the cell G4(which contains the formula) will show the corresponding information from N which reads 8990B

I'm currently working with this formula below:

=INDEX($N$1:$N$10,SUMPRODUCT(--(ISNUMBER(FIND($L$1:$L$10,D24)))*$N$1:$N$10))

But the cell will just gives me an error of #VALUE

May anyone help to look out whats the error with my formula.
Will it work as well if i change the cells from 'N' to 'O' if i need it to read the corresponding information from O instead?
Thanks! (Pardon my poor English and explanation)

Cheers,
Heva

[benishiryo]

welcome to the forum, Heva. not sure where you need to use the SUMPRODUCT. this will give you the answer:
=INDEX($N$1:$N$10,MATCH(D24,$L$1:$L$10,0))
even VLOOKUP is possible:
=VLOOKUP(D24,$L$1:$O$10,3,0)

since you used FIND, i'm assuming you might want partial match, so maybe:
=INDEX($N$1:$N$10,MATCH("*"&D24&"*",$L$1:$L$10,0))

do note that this has its risk. if "Alex" is replaced by "Benedict", that's the first "Ben" it'll find & return that result instead. if it doesn't help you, upload a sample excel file & show us the desired results you want. if you need to change from Column "N" to "O", the INDEX formula will work. the VLOOKUP formula will be like this:
=VLOOKUP(D24,$L$1:$O$10,4,0)

[Heva]

Hi Ben,

Thanks for you assistance.
As I am not trained in MS Excel formulas at all, so the formulas I am using as of current are bits and parts that I googled online and copy before editing them with trial and errors to fit into what I need.
So I was not really sure of how does the "FIND" function affects the result in the end too.
Thanks for explaining it to me.

Just another question, for VLOOKUP how does the number towards the end of the formula (3 or 4) will affect the result?

even VLOOKUP is possible:
=VLOOKUP(D24,$L$1:$O$10,3,0)

the VLOOKUP formula will be like this:
=VLOOKUP(D24,$L$1:$O$10,4,0)

[benishiryo]

you're very welcome. the lookup_value (D24) needs to be somewhere in the first column of the table_array ($L$1:$O$10). when it finds the value in the first column (i.e. L1:L10), you can choose to return the corresponding row in another column. to decide that will be where the 3 or 4 comes in. 3 would mean i want the 3rd column from L1:L10 & the would be N1:N10. 4 would be the 4th column & that would be O1:O10. hence, the table_array usually consist of at least 2 columns; 1 where you can can the lookup_value & the other where the desired results are. the 0 in the end simply means that you want an exact match. even a space found in the name "ben" will return an #N/A

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Heva]

I see
Thanks for the detailed explanation!
Will do so too!

Cheers