Vlookup or index/match on steroids

**[o_0] Bill** · 03-27-2013, 07:46 PM

How can I look for a value anywhere in an entire workbook and then return the value of the cell one to the right?

**Tank997** · 03-27-2013, 09:13 PM

Try Find, Ctrl+F go to the cell and copy and paste where you want it, if that doesn't work ask your friends on the moon

Or try rephrasing your question.

**[o_0] Bill** · 03-27-2013, 09:39 PM

Thanks Tank997. Sorry I was unclear...

Say I have a list of fruits in column A and a list of quanitities in column B. And say I want to find the number of apples...

In this case I could use =vlookup("apples",A:B,2,false) to get the number of apples (the value of the cell one to the right of "apples")

But what if "apples" is in a different column and I don't know which one? Or what if it is in a different sheet and I don't know which?

Is there any way to search the whole workbook without knowing the sheet or column that "apples" is in and return the cell one to the right.

You're right that I can find the value manually with Ctrl+F but I was hoping for a formula.

**Tank997** · 03-27-2013, 09:46 PM

Hmm.. Index and match could work if you were looking on 1 sheet but for all sheet I think you would need VBA. You may try posting an example workbook showing the before and after results either here or in a new post on the VBA board. If you post it on the VBA board you should mark this post as solved. Good Luck !