Formula to return value related to specific search term

[Padthelad]

Hi,

I have two worksheets in the same workbook that I want to be able to transfer data from one to the other.

The first worksheet is a ‘quote’ form. Which will have, quantity, type of material and size.

I would like the information from a range of cells to filter from the ‘quote’ to a ‘job sheet’.

For example:-

	A	B	C	D
1	QUANTITY	TYPE	SIZE	PRICE
2	1	Badminton	3x3	1000
3	2	Iden	4.8x4.8	1500
4	1	Cowbeech	3x3	2000

<tbody>
</tbody>

I need a formula that can search range A2:D4 for the type, say Badminton, and then return the size that relates to Badminton. I have a formula that gets the quantity which is:-

=IFNA(INDEX(Quote!$A$2:$D$4,MATCH(“Badminton”,Quote!$A$2:$D$4,0)),0)

I can’t seem to get it to work for what I need with the size. The problem occurs if I was to have two ‘types’ of different sizes. The amended formula fills two different cells with ‘3x3’ instead of only the one related to that ‘type’.

I hope this makes sense and any help anyone can provide is very much appreciated.

Thanks,

Pad

 

[JackDanIce]

For this problem, why can't you use VLOOKUP, since Size is to the right of the column being searched for "Badminton"? I.e.

=IFERROR(VLOOKUP("Badminton",Quote!$B$1:$D$4,2,0),0)

To return Size using INDEX+MATCH, edit in blue, try:

=IFNA(INDEX(Quote!$C$2:$C$4,MATCH("Badminton",Quote!$B$2:$B$4,0)),0)

 

[Aladin Akyurek]

Hard to follow...

What are you looking up and what are the associated results you are requiring?

 

[Padthelad]

Thank you Jack Dan Ice. This seems to be doing the trick for what we need at the moment.

Aladin - I was/am trying to return the related size of a 'type' to a cell in a different worksheet.

 

[JackDanIce]

You're welcome.