=IF(cell in a range = specific cell, return the correlating cell in another column, "N/A")

[richh]

I'm trying to do a price comparison on two lists of books. Both lists have books that are in both sheets and some that are not in the other. I took both BOOKS lists from sheet 1 and 2, pasted in sheet 3, and removed the duplicates. I would really like to see if theres a way just to return "Vendor 1 is cheaper" "Vendor 2 is cheaper" based on the cost of each title. However, I don't know if there's a way to do a conditional formatting that compares cells in two columns based off two other cell ranges equating to a single cell... ergo, I thought I'd just pull the values and do a greater than formatting.

I haven't dived too deep into Excel formatting, so bear with me if you look at what I've tried and go "lol this guy is a nincompoop..."

=IF(Sheet1!A:A=Sheet3!A1, Sheet1!B:B, "N/A")

=IF(ISNA(MATCH(Sheet3!A1,Sheet1!A:A,0)), Sheet1!B:B, 0) - this did the same thing as the previous statement I think. it seems to return the B:B value only if Sheet3's A value is in the same rownum as Sheet1's A value. I could return 1 or 0 for the true/false, just not the correlating cell value in another column.

Sheet 1 contains
BOOKS | PRICE
Book 1 | $5
Book 2 | $6
Book 3 | $7
Book 4 | $8

Sheet 2 contains
BOOKS | PRICE
Book 3 | $12
Book 4 | $11
Book 5 | $9
Book 6 | $10

Sheet 3 SHOULD contain
BOOKS | V1 PRICE | V2 PRICE
Book 1 | $5 | N/A
Book 2 | $6 | N/A
Book 3 | $7 | $12
Book 4 | $8 | $11
Book 5 | N/A | $9
Book 6 | N/A | $10

Any pointers you glorious geniuses can give me would be amazing.

 

[JBills]

Hi Rich,

Are you trying to avoid the error value "#N/A" that you should get with your formula? Otherwise, the final value in the IF formula is redundant. I'll write this assuming you do. In sheet 3, column B should have this formula =IFERROR(INDEX('Sheet 1'!B:B,MATCH($A2,'Sheet 1'!$A:$A,0),1),"N/A"), column C should have this formula =IFERROR(INDEX('Sheet 2'!B:B,MATCH($A2,'Sheet 2'!$A:$A,0),1),"N/A"). Paste them into B2 and C2 respectively and copy them down the range. With row 2 selected for columns B & C, go to Conditional Formatting, then the second option is Top/Bottom. Select that, then in the new menu, select "More Rules". Once the Formatting Options box opens up, change the drop-down value from Top to Bottom, and 10 to 1. This will apply your formatting to the lowest value in the pair. Click on the Formatting button to set your cell color, or font, or whatever you want to use to identify the lower price. Apply to the pair, copy and paste formatting across the range. Hope that helps!

J

 

[Jonmo1]

Sounds like VLOOKUP

=VLOOKUP(Sheet3!A1,Sheet1!A:B,2,FALSE)

 

[richh]

I've tried the VLOOKUP, but it returns #N/A on every row. My workbook's sheets are a bit different than my example, but I didn't think it'd be that big a difference. For example, Sheet1's BOOKS column is I and the PRICE is M. I've tried =VLOOKUP(Sheet3!A2, Sheet1!A:M, 13, FALSE), but it doesn't seem to work.

@JBills - What I'm trying to do is just return the price of the book for both vendors, if there is one. If the vendor doesn't offer the book, I want to see "N/A" in their respective price column. I'm not really trying to avoid any kind of error; I just want to see if the book referenced exists and, if so, their prices.

 

[JBills]

That makes sense. If you adjust the formula I gave you, you should be able to avoid any error values. I don't know how familiar you are with index and match. The Index column should have the values you want to return, and the match column should be where the value is stored for your reference value. In Sheet1 example you gave, you would use M:M for the Index column, and I:I for the match. One of the main advantages Index & Match has over VLOOKUP is that you can reference any column in the sheet, it doesn't have to be to the left of your return values as you do with VLOOKUP. You also don't have to count columns.

 

[richh]

Thanks JBills!

=IFERROR(INDEX(Sheet1!M:M,MATCH($A2,Sheet1!$I:$I,0),1),"N/A") - that works near flawlessly! it does sometimes return "0" instead of a "N/A", even when the book isn't listed on the Sheet1, but I think I can live with that. Thanks for your help!

 

[JBills]

You're welcome! I'm a little puzzled by the response. The way that formula works, if it matches a value in sheet3 to one in sheet1, it will return the value. If there's no match, it will return N/A. If you're getting a value of 0, that should mean that the book is on the sheet with a price of $0, or that you aren't returning an exact match. It looks like you adjusted the formula correctly. Did you try a control+F to find the value in that sheet? Maybe it's further down the page?

 

[richh]

lol JBills - I had the 0 prices filtered out... herp derp..