Copy the first cell in the same row as a index/match value

I have a worksheet where I am trying to get a cell copied from the same row as a value found using index/match. The issue is that there are several blank cells and the number of columns from the first column that I need copied varies.

The idea is that a recipe is selected from a dropbox and the ingredients are automatically listed along with how many of each item is needed.
I have tried using offset, index/match with Rows and Columns, and if statements using index and match. The If-Index/match has gotten me close but I can not figure out how to make it scale down across the rows.

I have attached an example worksheet with out the spaghetti of formulas I have been tinkering with.

Hi Alterios,
Welcome to the forum

with a helper column


J2
= INDEX(A$1:F$8,ROW(),MATCH(H$1,A$1:F$1,0))
fill down J8

H2
=IF(ISERROR(INDEX($A$1:$A$8,SMALL(IF(ISTEXT($A$1:$A$8)*(J$1:J$8>0), ROW($A$1:$A$8),""),ROW(1:8)))),"",INDEX($A$1:$A$8,SMALL(IF(ISTEXT($A$1:$A$8)*(J$1:J$8>0), ROW($A$1:$A$8),""),ROW(1:8))))Ctrl + Shift+ Enter
fill down H8


I2
=IF(ISERROR(INDEX(B$2:F$8,MATCH(H2,A$2:A$8,0),MATCH(H$1,B$1:F$1,0))),"",INDEX(B$2:F$8,MATCH(H2,A$2:A$8,0),MATCH(H$1,B$1:F$1,0)))fill down to I8

not pretty but will do the job

Here is solution IF values in some column are unique (for example under Soda ALL unique numbers)..

So put difference at least 0,01

Perfect Zbor

No need to have a helper column

using zbors offset code
H2
=IF(ISERROR(INDEX($A$1:$A$8,SMALL(IF(ISTEXT($A$1:$A$8)*(OFFSET($A$1:$A$8,0,MATCH($H$1,$B$1:$F$1,0))>0), ROW($A$1:$A$8),""),ROW(1:8)))),"",INDEX($A$1:$A$8,SMALL(IF(ISTEXT($A$1:$A$8)*(OFFSET($A$1:$A$8,0,MATCH($H$1,$B$1:$F$1,0))>0), ROW($A$1:$A$8),""),ROW(1:8))))
Control + Shift Enter
fill down to H8

The mesurements can be the same

Pike and Zbor, that is amazing!
Thank you for your help and the warm welcome. I have used your forums for many, many projects in the past, it is always one of my first stops when I need a formula.

This is exactly what I need. Now to spend a day or two figuring out how it works. It has solved my problem but can you please give a breakdown of how it work so anyone that comes across this (including me) knows how this formula works?
P.S. I don't know how to change the title to Solved so can a moderator let me know how to fix it? Thanks again

Also, See the attached.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Alterios

Haseeb is even shorter

=IFERROR(INDEX($A:$A,SMALL(IF($A$2:$A$8<>"",IF(INDEX($B$2:$F$8,,MATCH($H$1,$B$1:$F$1,0))>0,ROW($A$2:$A$8))),ROWS(H$3:H3))),"")

How does Haseeb array work
=IFERROR(INDEX($A:$A,SMALL(IF($A$2:$A$8<>"",IF(INDEX($B$2:$F$8,,MATCH($H$1,$B$1:$F$1,0))>0,ROW($A$2:$A$8))),ROWS(H$3:H3))),"")

First trick is to display the array constants using F9
=IFERROR(INDEX($A:$A,SMALL(IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},IF({11;0;0;14;15;0;0}>0,{2;3;4;5;6;7;8})),1)),"")
iferror just to negate NA values

which leaves
index($a:$a,small(if({true;true;true;true;true;true;true},if({11;0;0;14;15;0;0}>0,{2;3;4;5;6;7;8})),1))

The index first argument index - the array values in column A to show

Index's second argument Row is not need as we use all the rows in the selected column

Index's third arg - which column in array B2:F8

The match will find that column
match($h$1,$b$1:$f$1,0) gives the column number

The small in array with if coheres the values
small(if({true;true;true;true;true;true;true},if({11;0;0;14;15;0;0}>0,{2;3;4;5;6;7;8}

The ROWS(H$3:H3) with the small
determines which value to display from array in the filled down row on worksheet

hope it helps