vlookup & if statement

L

Legacy 15162

Guest
I am trying to perform a vlookup function & an if statement in the same cell. if a cell from spreadsheet 1 is found on spreadsheet 2 then check on the same row if cell j2 = l2. if cell j2 = l2, if true then check and see if j2 is > k2 by more than 10, change the color of cell in column w to green.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi txksa:

A little clearer description would help in understanding what you are trying to accomplish. Can you specify cell references you are comparing in Sheet1 and sheet2 -- in fact it will really help if you can post some sample data, showing what formula(s) you have used, what result you got, and what you think is the right result -- and then let us take it from there.
 
Upvote 0
Problem is I am not sure how to write this properly.....
First part:
=vlookup(T2,Sheet1!G2:G1558,
Second part:
ex: if the vlookup shows that T2 matches Sheet1G8, then I want the formula to check if Sheet1 J8 = Sheet1 L8 (different column but same row as Sheet1 G8)
Third part:
ex: if sheet1 j8 = sheet1 L8 then I want to check and see if Sheet1 J8 > Sheet1 K8 by more than $10, I want the value of the box to return false and change the color of the cell to green
 
Upvote 0
Well, how about if we take it part by part. So, let us look at the first part --

what are you trying to do with the VLOOKUP function? -- where T2 is the lookup value, your lookup range is Sheet1!G8:G558, then what? You see you have to specify that after matching T2 with data in the range G8:G558, determine which row the match occurs in , and then lookup the corresponding value in a particular column in the lookup range; since you have only one column in the lookup range, your Column number is 1; then you have to consider, whether you are looking for an exact match or to ensure that T2 lies within a range of values;

anyway, you may or may not need VLOOKUP. It is better for you to describe in words, what are you trying to do in the first part.

If the problem can be understood clearly, I am sure you will get meaningful help for what you are trying to do.
 
Upvote 0
sorry about that...for the first part....if cell T2 corresponds with a value in sheet1 column G, I want to formula to only check data for the second part on the corresponding row.
 
Upvote 0
Hi txksa:

I would have prefered you build the solution part by part -- however, since you have provided a very specific and clear description, the single formula to do what you want is:

=IF(INDEX($G$2:$L$1558,MATCH($T$2,$G$2:$G$1558,0),COLUMNS($G:J))=INDEX($G$2:$L$1558,MATCH($T$2,$G$2:$G$1558,0),COLUMNS($G:L)),IF(INDEX($G$2:$L$1558,MATCH($T$2,$G$2:$G$1558,0),COLUMNS($G:J))-INDEX($G$2:$L$1558,MATCH($T$2,$G$2:$G$1558,0),COLUMNS($G:K))>10,FALSE,"SomeThingElse"))

Please note I used the MATCH function in what you called part1 rather than using the VLOOKUP function that you were contemplating to use.

Then since I am using cell O4 to record the value, for Conditional Formating of cell O4, I used ...

Formula Is ... =O4=FALSE ... then FORMAT|Patterns -- green color

see the following simulation where the formula has been applied ...
y030423h1.xls
GHIJKLMNOPQRST
1
21111117
3211111
4311111FALSE
5411111
671114314
7611111
851114314
9811111
10911111
Sheet1


Is this what you are looking for?

Please post back if it works for you now -- otherwise explain a little further and let us take it from there.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top