Index Match with an IF

[ABG123]

Hi all
Hope everybody is well.
I am currently trying to build a quote sheet that looks up values based on a quote number.

Example data sheet below, so I would add in the "1's" where I needed them:

And I am trying to get it to populate Column C with the "1" on a different tab, based on the number in Cell P1

So if I were to amend that number to in P1 "55", I would want the "1's" to appear in Test 1, Test 3 and Test 5.

I can index match, but can't seem to figure out how to do it!

All help appreciated.

Thanks in advance.

 

[Fluff]

How about

=INDEX(Sheet1!$C$2:$G$10,MATCH($P$1,Sheet1!$A$2:$A$10,0),MATCH(B2,Sheet1!$C$1:$G$1,0))

 

[ABG123]

How about

=INDEX(Sheet1!$C$2:$G$10,MATCH($P$1,Sheet1!$A$2:$A$10,0),MATCH(B2,Sheet1!$C$1:$G$1,0))

Hi Fluff, thanks for the quick reply. Will give that a go ASAP. For my future information, can you please advise what the B2 is doing in the final match? Thank you

 

[Fluff]

That's a typo & it should be A2.

 

[ABG123]

That's a typo & it should be A2.

Unfortunately didn't work, it pulled back a 0 instead of a 1 for some reason

 

[Fluff]

In that case can you post some sample data from both sheets.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ABG123]

In that case can you post some sample data from both sheets.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thank you very much, sir. Here we go! So the first one is the Front Sheet, where I would like to amend Cell H2 to different quote numbers (I created a new dummy sheet for ease)

Example Workbook.xlsx
	A	B	C	D	E	F	G	H
1	Product	Cost	QTY				Quote	42
2	Product 1	£5.00
3	Product 2	£10.00
4	Product 3	£15.00
5	Product 4	£20.00
6	Product 5	£25.00
7	Product 6	£30.00
8	Product 7	£35.00
9	Product 8	£40.00
10	Product 9	£45.00
Pricings

The next one is my data sheet, where I will be putting values under the products:

Example Workbook.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Quote Number	Customer	Date	Product 1	Product 2	Product 3	Product 4	Product 5	Product 6	Product 7	Product 8	Product 9
2	42	Telly Ltd	08/08/2022	1			2			1
3	43	Fabien	08/08/2022		1
4	44	Humbert & Co	08/08/2022	1			1
Data

Thank you for your help so far! Much appreciated!

 

[Fluff]

Thanks for that, the formula I suggested works for

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Product	Cost	QTY				Quote	42
2	Product 1	5	1
3	Product 2	10	0
4	Product 3	15	0
5	Product 4	20	2
6	Product 5	25	0
7	Product 6	30	0
8	Product 7	35	1
9	Product 8	40	0
10	Product 9	45	0
Main

Cell Formulas
Range	Formula
C2:C10	C2	=INDEX(Data!$D$2:$L$4,MATCH($H$1,Data!$A$2:$A$4,0),MATCH(A2,Data!$D$1:$L$1,0))

Did you change the sheet name & ranges to suit?

 

[ABG123]

Thanks for that, the formula I suggested works for

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Product	Cost	QTY				Quote	42
2	Product 1	5	1
3	Product 2	10	0
4	Product 3	15	0
5	Product 4	20	2
6	Product 5	25	0
7	Product 6	30	0
8	Product 7	35	1
9	Product 8	40	0
10	Product 9	45	0
Main

Cell Formulas
Range	Formula
C2:C10	C2	=INDEX(Data!$D$2:$L$4,MATCH($H$1,Data!$A$2:$A$4,0),MATCH(A2,Data!$D$1:$L$1,0))

Did you change the sheet name & ranges to suit?

Amazing! All sorted. Thank you very much. Must have been my human error after your first message. Very much appreciate your time. Have a great day.

 

[Fluff]

You're welcome & thanks for the feedback.