UniqueUsername
New Member
- Joined
- Dec 9, 2022
- Messages
- 9
- Office Version
- 365
- Platform
- Windows
REPOSTED from:
www.mrexcel.com
I build my spreadsheets in EXCEL and then once I've laid everything out the way I like, I build it in Google Sheets. I've never ran into an issue until today. I posted the above question in the Excel forms and received a great answer, but it only works in Excel. Below is the exact question. The screen shot is from Excel, but is just there to help visualize what I'm trying to accomplish.
Basically, I want to:
-use VLOOKUP on A2:B4
-if the item exists and the price isn't empty, return the price
-if not found in A2:B4, or price is blank, check C2:D4
-if that fails, run a different set of calculations that are rather long and would likely exceed formula length if I have to input them multiple times into the same formula
I have a table that due to sizing issues, I've split in 2 and placed side by side.
To simplify things, I'll use a Fruit/Price example. Column A&C are Fruit, Column B&D are the price of the fruit in the previous column.
I need to complete a VLOOKUP on the fruit to return the price. If the fruit is listed without a price or if the fruit isn't listed at all, I have additional calculations to complete.
My problem is trying to create a formula that checks whether the VLOOKUP produces an error or returns a blank.
I've tried
=IF(OR(ISNA(VLOOKUP("ORANGE", A2:B4,2,FALSE)),VLOOKUP("ORANGE",A2:B4,2,FALSE)),"TRUE","FALSE")
While the first part of the OR statement is true, ORANGE isn't found in the vlookup, the formula returns #N/A because the second part of the formula fails. How do I get the OR to stop calculating at the first "TRUE" statement?
Ultimately, the idea is to do the same search on C:D and complete other calculations if that fails as well.
Any ideas would be appreciated!
VLOOKUP with IF OR returning N/A or BLANK
I have a table that due to sizing issues, I've split in 2 and placed side by side. To simplify things, I'll use a Fruit/Price example. Column A&C are Fruit, Column B&D are the price of the fruit in the previous column. I need to complete a VLOOKUP on the fruit to return the price. If the...
I build my spreadsheets in EXCEL and then once I've laid everything out the way I like, I build it in Google Sheets. I've never ran into an issue until today. I posted the above question in the Excel forms and received a great answer, but it only works in Excel. Below is the exact question. The screen shot is from Excel, but is just there to help visualize what I'm trying to accomplish.
Basically, I want to:
-use VLOOKUP on A2:B4
-if the item exists and the price isn't empty, return the price
-if not found in A2:B4, or price is blank, check C2:D4
-if that fails, run a different set of calculations that are rather long and would likely exceed formula length if I have to input them multiple times into the same formula
I have a table that due to sizing issues, I've split in 2 and placed side by side.
To simplify things, I'll use a Fruit/Price example. Column A&C are Fruit, Column B&D are the price of the fruit in the previous column.
I need to complete a VLOOKUP on the fruit to return the price. If the fruit is listed without a price or if the fruit isn't listed at all, I have additional calculations to complete.
My problem is trying to create a formula that checks whether the VLOOKUP produces an error or returns a blank.
I've tried
=IF(OR(ISNA(VLOOKUP("ORANGE", A2:B4,2,FALSE)),VLOOKUP("ORANGE",A2:B4,2,FALSE)),"TRUE","FALSE")
While the first part of the OR statement is true, ORANGE isn't found in the vlookup, the formula returns #N/A because the second part of the formula fails. How do I get the OR to stop calculating at the first "TRUE" statement?
Ultimately, the idea is to do the same search on C:D and complete other calculations if that fails as well.
Any ideas would be appreciated!
