# How to find corresponding cells in row based on lookup?

I have 5 rows with 10 columns. Below this 5x10 grid, I populate a cell with a value that is listed in column 1 of the grid. "ABC" for example.

If "ABC" is row 3, column 1, I want to them get the values in row 3, columns 4, 5, 6. Those three values will go into cells just above or below the "ABC" cell that is below the grid.

The value where "ABC" is will change, as I get info from different rows, but will always correspond to a value in column 1 of the 5x10 grid. Any ideas on how to do this?

• Please read how to and share a test sheet so as you can be easier helped. Commented Mar 11, 2020 at 14:51
• There are other, simpler ways to do that. Is `LOOKUP` a must? Commented Mar 11, 2020 at 16:21

In Test Sheet A10 is your "ABC" cell and below find the formula that returns Values from columns 4,5,6 (or any other - just add)

``````=IFERROR(QUERY({Sheet1!\$A1:\$J6},
"select Col4, Col5, Col6
where Col1 ='"&A10&"'",0),"no data")
``````
• Perfect! By the way, did you share that sheet anonymously? I don't see any identifying info on it. If so, can you say how? Commented Mar 11, 2020 at 17:34
• @4thSpace I just get shareable link, so anyone on the internet can see it. you can find info on Share\Advanced Commented Mar 12, 2020 at 7:11

The simplest way

``=if(A8=A4,{D4,E4,F4})  ``

No need to over complicate things

• Of course, not flexible. Commented Mar 11, 2020 at 17:41
• Your question is "... I want to them get the values in row 3, columns 4, 5, 6." Those values are not flexible Commented Mar 11, 2020 at 17:43