Find a value within a range of many columns and rows (eg. A1:Z1000)

[[email protected]]

How do we find a value within a large range of cells (eg. Find "0001ABC" within A1:Z1000) and return the value of the cell right next to that value (eg. if you manage to find "0001ABC" which could be anywhere within A1:Z1000, give the value of the cell right next to it)?

Many thanks!

[James McEwan]

Hi Dan.carpe,

This is a simple index/match. You can look up how to use this in further detail online.

Please find attatched the solution you were looking for.

Hope it helps,

J.

ef - Dan.carpe 22 05 2015.xlsx

remember to hit add rep, and mark as solved in your original thread

[FDibbins]

@ James, Im not sure that is what the OP is asking for. It sounds like the search value could be anywhere in the table, not just in the 1st(?) column

@ Dan, can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[Tony Valko]

Define "right next to it".

To the left? The right? Above? Below?

Can there be duplicates of the lookup value?

[[email protected]]

hi, if the value is found to be in cell F200 for example, I would want to return the value in cell G 200.

It would only occur once within the multiple rows/columns.

[[email protected]]

Here's a sample: want to find "Cindy" and return "green"

10 APE ORANGE 235
22 CINDY GREEN CCDE110
HER 10A GOLD JANUARY

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E	F	G
1	10	APE	ORANGE	235		Cindy	GREEN
2	22	CINDY	GREEN	CCDE110
3	HER	10A	GOLD	JANUARY
4	------	------	------	------	------	------	------

This array formula** entered in G1:

=INDEX(A1:D3,MAX(IF(A1:D3=F1,ROW(A1:D3)))-ROW(A1)+1,MAX(IF(A1:D3=F1,COLUMN(A1:D3)))-COLUMN(A1)+2)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[[email protected]]

Thanks Tony,

Would that work if I change the lookup word to "gold" to find "January" for example, without needing to change the +1 or +2 row/column references?

Many thanks

[Tony Valko]

Would that work if I change the lookup word to "gold" to find "January" for example, without needing to change the +1 or +2 row/column references?

Yes! ______