I would like to have cell F11 display a specific value based on what the contents of cell C11 are.

For example, if cell C11 were to display “Mental Health”, then F11 should display “Paula”, whereas if cell C11 were to display “Pharmacy”, then F11 should display “Lisa”.

However, I should point out that the value of C11 could be any one of 39 values, and the values of F11 could be any one of 5 names.

Now, I know how to create nested IF statements, but given the volume of possibilities / data, this won’t work due to argument limits being breaches. I have named certain groups of C11 values (let’s call them Areas) with people’s names (F11 values) so that each person oversees a certain Area. The Areas are named something along the lines of lisaArea, paulaArea, etc..

The value of C11 is selected via a drop-down which I have inserted via the data validation tool and I require the value of F11 to update automatically to reflect which person is linked to the Area under scrutiny.

Please note that, ideally, I would like to be able to succeed with this without using VBA, but if there is no other way other then VBA, then so be it.

If any further info is required, please let me know.

Many thanks.

  • 1
    I think what you want is VLOOKUP or HLOOKUP. – Alvin Wong Jan 9 '13 at 13:53
  • I see; I shall try and get back to you with my results. – SnookerFan Jan 9 '13 at 13:56
  • No, neither of these are able to produce the desired result. It must be possible in VBA, but I don't know how I would go about it. – SnookerFan Jan 9 '13 at 14:01
  • I am not quite sure then. I think they will do what you want, but will require some extra space for placing the lookup table. Yet cleaner than using nested IFs. If that is really not what you want, you may need to clarify more. – Alvin Wong Jan 9 '13 at 14:04
  • @SnookerFan: I also think that the Lookup solution should work. If not, there is the construct SELECT CASE ... in VBA, and you could define your own function which reads the value from a cell and returns the corresponding other value. But basically, that's what Lookup also does... Could you post an example Excel file to show what you need? That would make helping much easier. – MostlyHarmless Jan 9 '13 at 14:09

You can try to use either VLOOKUP or HLOOKUP. It returns data by looking up a value in a range of cell.

For example:

    A       B
1   input1  output1
2   input2  output2
3   input3  output3
4   input4  output4
5   input5  output5
6   input6  output6
7   input7  output7
8   input8  output8
9   input9  output9
10  input10 output10

The formula =VLOOKUP("input4",A1:B10,2) will show output4.

HLOOKUP works similarly, but with a horizontal lookup table.

  • 1
    I tweaked it slightly (as the LUTs are in another tab) but it worked based on your example. Thank you. – SnookerFan Jan 9 '13 at 15:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.