If text exists return value in corresponding, adjacent cell

[Padthelad]

Hi,

HELP PLEASE!

I am having trouble using a combination of IF and VLOOKUP functions.

I have a quote form that I want to filter through to workshop sheet in the same workbook. I want to be able to search a range of cells on the quote form for an item, say MDF, and then return the quantity in the corresponding cell adjacent to MDF to the workshop order form.

For example (see example below); I want to be able to write a formula that can search range of cells A2:D4 for 'MDF' and then return the value that corresponds to 'MDF' (in this case cell A2, 24).

I have been using IF and COUNTIF, but can only get the formula to return a true/false (YES/NO) expression. I have been using =IF(COUNTIF(A2:D4,"MDF"),"yes"). I am thinking that I need the "yes" part of this formula to perform a VLOOKUP function to return 'If MDF then return value in cell next to MDF'

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Quantity[/TD]
[TD]Description[/TD]
[TD]Price[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]24[/TD]
[TD]MDF[/TD]
[TD]2[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Any help anyone can provide is very much appreciated, as I am going around in circles!

Thank you.

Pad

 

[steve the fish]

This will do that:

=INDEX($A$2:$A$10,MATCH("MDF",$B$2:$B$10,0))

 

[BarryL]

am I missing the reason why you cant have =IF(COUNTIF(A2:D4,"MDF")>0,A2,"")

or will it not always be in column B?

 

[Padthelad]

Thank you Steve! That was a really quick and perfect response!

 

[steve the fish]

am I missing the reason why you cant have =IF(COUNTIF(A2:D4,"MDF")>0,A2,"")

or will it not always be in column B?

You could if you only wanted to return A2. Depends if you need to return the value in the correct row where its found I suppose.

 

[Padthelad]

Hi,

I have encountered another issue with this spreadsheet. On the quote form I have another column that has size of MDF in it. I would like to be able to filter through to another sheet what size MDF. See below:-


[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Quantity[/TD]
[TD]Description
[/TD]
[TD]Size
[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]24[/TD]
[TD]MDF[/TD]
[TD]3x3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10
[/TD]
[TD]Ply
[/TD]
[TD]3.6x3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I would like to be able to return the size in column C in the 'MDF' cell on another sheet, providing there is a quantity in the 'MDF' row in column A on this sheet.

I hope this makes sense.

Thanks,

Pad
.