Yes or blank, depending if value equals or exceeds value found inVLookup table.

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi,

I am trying to have cell E2 display "Yes" if cell G2 is equal to or over a certain amount. This amount is determined by the text in cell C2. The text in cell C2 is tied to a Vlookup table on a sheet named "Index".

For example, the text in cell C2 is "black", and the value in the column next to the word "black" on the Vlookup is 100. The value in cell G2 is 90, and since 100 is greater then 90, Cell E2 would display "yes".

Conversely, the text in cell C2 is "brown", and the value in the column next to the word "brown" on the Vlookup is 80. The value in cell G2 is 90, and since 80 is less then 90, Cell E2 would be blank.

Thanks for any help...
 
Hi,



I am trying to have cell E2 display "Yes" if cell G2 is equal to or over a certain amount. This amount is determined by the text in cell C2. The text in cell C2 is tied to a Vlookup table on a sheet named "Index".



For example, the text in cell C2 is "black", and the value in the column next to the word "black" on the Vlookup is 100. The value in cell G2 is 90, and since 100 is greater then 90, Cell E2 would display "yes".



Conversely, the text in cell C2 is "brown", and the value in the column next to the word "brown" on the Vlookup is 80. The value in cell G2 is 90, and since 80 is less then 90, Cell E2 would be blank.



Thanks for any help...
Click to expand...

Hi Steve,

Try this in E2 which will give you a "yes" for your "black" statement and by default a blank for the "brown" statement.

=IF(AND(C2="black",D2>G2),"yes","")

Regards,
Howard
 
Hi,



I am trying to have cell E2 display "Yes" if cell G2 is equal to or over a certain amount. This amount is determined by the text in cell C2. The text in cell C2 is tied to a Vlookup table on a sheet named "Index".



For example, the text in cell C2 is "black", and the value in the column next to the word "black" on the Vlookup is 100. The value in cell G2 is 90, and since 100 is greater then 90, Cell E2 would display "yes".



Conversely, the text in cell C2 is "brown", and the value in the column next to the word "brown" on the Vlookup is 80. The value in cell G2 is 90, and since 80 is less then 90, Cell E2 would be blank.



Thanks for any help...
Click to expand...

Missed the EQUAL TO part...

=IF(AND(C2="black",D2>=G2),"yes","")

Howard
 
Missed the EQUAL TO part...



=IF(AND(C2="black",D2>=G2),"yes","")



Howard
Click to expand...

Thanks Howard, however; this did not work. The Vlookup table on a sheet called "Index" contains the threshold value that "black" or "brown" has associated to it, and the formula you gave me doesn't reference this table. The absolute reference to this table is: VLOOKUP(C2,Index!$I$2:$J$100,2,0)
 
Thanks Howard, however; this did not work. The Vlookup table on a sheet called "Index" contains the threshold value that "black" or "brown" has associated to it, and the formula you gave me doesn't reference this table. Theabsolute reference to this table is: VLOOKUP(C2,Index!$I$2:$J$100,2,0)
Click to expand...

I finally got back to messing around with this spreadsheet, and I figured it out.
I am posting the solution that worked for me, just in case it could benefitsomeone else:

=IF(ISERROR(G2>(VLOOKUP(C2,Index!$I$2:$J$100,2,0))),"",IF(G2>(VLOOKUP(C2,Index!$I$2:J$100,2,0)),"Y",""))
 
Back
Top