Using lookup to find the 1st, 2nd, 3rd and 4th value

[Excel_apprentice]

In this spreadsheet, I have one worksheet with a list of customers and another worksheet with a list of their orders. Sheet 1 has the customer name in one row, sheet 2 has a separate row for each order the customer has placed. I want to return all the order details from sheet 2 into sheet 1, but have them on one row. Columns B-I in sheet 1 are where I would like this to be populated in.

Thank you in advance.

[nick.williams]

Try this:

B2=IFERROR(INDEX(Sheet2!$B:$C,SMALL(IF(Sheet2!$A$2:$A$11=Sheet1!$A2,ROW(Sheet2!$A$2:$A$11),""),(COLUMN()-COLUMN($B$1))/2+1),MOD(COLUMN()-COLUMN($B$1),2)+1),"")

Copy across and down.

[Fluff13]

Another option in B2 dragged down

Please Login or Register  to view this content.

[Czeslaw]

Formula
price

Please Login or Register  to view this content.

Subject

Please Login or Register  to view this content.

[HansDouwe]

I'll tried the solution of Nick, but 4 prices stays empty.
Am I doing something wrong?

[Excel_apprentice]

Try this:

B2=IFERROR(INDEX(Sheet2!$B:$C,SMALL(IF(Sheet2!$A$2:$A$11=Sheet1!$A2,ROW(Sheet2!$A$2:$A$11),""),(COLUMN()-COLUMN($B$1))/2+1),MOD(COLUMN()-COLUMN($B$1),2)+1),"")

Copy across and down.

Can't get this to work, are you entering this into cell B2 in sheet 1? If so, don't formulas need to start with '='?

[Excel_apprentice]

Another option in B2 dragged down

Please Login or Register  to view this content.

This works great, just trying to see how to adapt it to the sheet I'm using (which I can't share for data protection reasons). Assuming the columns are in the same order, which parts do you think I need to change? I've tried changing the sheet names, but still can't get it to work.

Thanks so much.

[Excel_apprentice]

Formula
price

Please Login or Register  to view this content.

Subject

Please Login or Register  to view this content.

This works in this scenario, but if I were to amend the prices so two products have the same price it no longer appears to work. In the real sheet I'm using, there will be different products with the same price.

[Fluff13]

Assuming the columns are in the same order, which parts do you think I need to change?

I've no idea without seeing the actual sheet. Can you post something more realistic of your actual data.

[Fluff13]

Am I doing something wrong?

Nope, it's the formula

[wk9128]

worksheet Or Tab name : Sheet1

Cell B2 formula , Drag down and across

Please Login or Register  to view this content.

[nick.williams]

I'll tried the solution of Nick, but 4 prices stays empty.
Am I doing something wrong?

You're right, I missed a FLOOR that should have been there.

=IFERROR(INDEX(Sheet2!$B:$C,SMALL(IF(Sheet2!$A$2:$A$11=Sheet1!$A2,ROW(Sheet2!$A$2:$A$11),""),FLOOR((COLUMN()-COLUMN($B$1))/2,1)+1),MOD(COLUMN()-COLUMN($B$1),2)+1),"")

[nick.williams]

Can't get this to work, are you entering this into cell B2 in sheet 1? If so, don't formulas need to start with '='?

Yes, enter into B2, don't type the B2 again. It had a small error, please see post 12.

[wk9128]

worksheet Or Tab name : Sheet1

Cell B2 formula , Drag down and across

Please Login or Register  to view this content.

[Excel_apprentice]

I've no idea without seeing the actual sheet. Can you post something more realistic of your actual data.

I've changed the column order so it matches, just in case that was the problem.

[Excel_apprentice]

You're right, I missed a FLOOR that should have been there.

=IFERROR(INDEX(Sheet2!$B:$C,SMALL(IF(Sheet2!$A$2:$A$11=Sheet1!$A2,ROW(Sheet2!$A$2:$A$11),""),FLOOR((COLUMN()-COLUMN($B$1))/2,1)+1),MOD(COLUMN()-COLUMN($B$1),2)+1),"")

Thank you for this. I've been unable to replicate it in the sheet with the real data and I'm hoping this is because the columns are in a different order. I'm not sure which bits to change to match it up other than the sheet names. Could you possibly take a look at this attached sheet and tweak the formula so it matches?

[Excel_apprentice]

worksheet Or Tab name : Sheet1

Cell B2 formula , Drag down and across

Please Login or Register  to view this content.

Thank you for this. I've been unable to replicate it in the sheet with the real data and I'm hoping this is because the columns are in a different order. I'm not sure which bits to change to match it up other than the sheet names. Could you possibly take a look at this attached sheet and tweak the formula so it matches?

[nick.williams]

Yes, you'd just need to change the columns. So for your new sheet it would be:

=IFERROR(INDEX(Sheet2!$I:$J,SMALL(IF(Sheet2!$U$2:$U$11=Sheet1!$A2,ROW(Sheet2!$U$2:$U$11),""),FLOOR((COLUMN()-COLUMN($E$1))/2,1)+1),MOD(COLUMN()-COLUMN($E$1),2)+1),"")

[nick.williams]

Or a potentially more robust variation as it allows you to have the subjects and prices in columns that aren't next to each other:

=LET(schools,Sheet2!$U$2:$U$11,subjects,Sheet2!$I$2:$I$11,prices,Sheet2!$J$2:$J$11,ref_cell,$E$1,IFERROR(INDEX(CHOOSE({1,2},subjects,prices),SMALL(IF(schools=$A2,ROW(schools)-MIN(ROW(schools))+1,""),FLOOR((COLUMN()-COLUMN(ref_cell))/2,1)+1),MOD(COLUMN()-COLUMN(ref_cell),2)+1),""))

In this version you just need to set the ranges for your Sheet2 columns (coloured red) which are at the start of the formula, and also set a reference cell which should be in the first column where you want the output.

[Excel_apprentice]

Yes, you'd just need to change the columns. So for your new sheet it would be:

=IFERROR(INDEX(Sheet2!$I:$J,SMALL(IF(Sheet2!$U$2:$U$11=Sheet1!$A2,ROW(Sheet2!$U$2:$U$11),""),FLOOR((COLUMN()-COLUMN($E$1))/2,1)+1),MOD(COLUMN()-COLUMN($E$1),2)+1),"")

Thanks. When I change the sheet names and try that in the real sheet I keep getting a pop up telling me to update values for the worksheet I'm looking up against. I've tried changing the sheet name and making sure there's no spaces, but that didn't fix it. A bit puzzling.

[Fluff13]

How about

Please Login or Register  to view this content.

[wk9128]

ANS. POST#17

worksheet Or Tab name : Sheet1

Cell E2 formula , Drag down and across

Please Login or Register  to view this content.

[Czeslaw]

Power Query

Please Login or Register  to view this content.

[nick.williams]

Thanks. When I change the sheet names and try that in the real sheet I keep getting a pop up telling me to update values for the worksheet I'm looking up against. I've tried changing the sheet name and making sure there's no spaces, but that didn't fix it. A bit puzzling.

I'm strugling to reproduce your problem. Is it in a different workbook rather than just a different sheet? I'm not sure if that might cause issues.