Lookup function error (google sheets)

**WAAZUPEE** · 12-14-2017, 10:34 AM

LOOKUP2.jpg

This is what I have come up with so far but returning an error

=IF(ISNUMBER(SEARCH(C59, C2:C)), VLOOKUP(C59,E2:E, 2))

Returning out of range error

I would like to do a search for the value in C59 (123456) in all of column C - if that value is found return the value in column E of that row

IE: search for 123456 - returns 56789 in E59

Thanks for you help

Mike

**xlnitwit** · 12-14-2017, 10:39 AM

Hi,

You're telling it to return the value from the second column of a one column range. You should be using a formula like
=VLOOKUP(C59,C2:E, 3)
I'm not familiar enough with Google sheets to determine if that range is valid or if you should use C:E rather than C2:E

**MrShorty** · 12-14-2017, 11:22 AM

I am not sure where the SEARCH() function comes from. As explained in Google's help file, the SEARCH() function is used to find a text string within a larger text string: https://support.google.com/docs/answer/3094154 which is not what you appear to be trying to do. Unless I am misunderstanding something, the SEARCH() function does not seem to belong to your desired task.

I would recommend that you spend some time with the VLOOKUP() help file (https://support.google.com/docs/answer/3093318 ) to better understand how this function is used. As xlnitwit noted, one of the first mistakes to be noted is that you are asking the function to return a value from the second column of a one column range, which should trigger an error.