How to look for value in three different ranges and return one of three results
Requirement:
The user would like to do is use a formula to compare a cell against a range and then return the value from another cell.
The user would like a formula that goes into cell A5 which looks at the value in cell B5 and looks it up in one of the columns (either I4:I6, K4:K5, M4:M6) Depending on which range it is found in it then returns the value in either I3, K3 or M3.
If the value isn't in the list the user needs it to put the word 'other'.
Solution:
=IFERROR(VLOOKUP(B5,$I$5:$J$12,2,FALSE),"Other")
Obtained from the OzGrid Help Forum.
Solution provided by NBVC.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use match formulae: input data range based on cell value |
| How to first average a range and then round the value |
| How to find the maximum date within date range |
| How to use Index/Countif based on multiple factors including range |
| How to print range excluding blank rows with formulas |
| How to sum up values in a date range |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
