• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Vlookup then find a value in different row

M

Mal

New Member
I'm trying to create a formula to find a value (see attachment) in a different row. I know there are different ways of doing this but identifying the best one is proving difficult. I'm fine with standard vlookup and also index/match formulas but i've been told that I should be using an offset array formula to get my result but i'm unsure on how to do it. Firstly i need to select a Town then select a line then the result should be a choice of either Calls, HO or FCR. Any help on how to do this would be appreciated!
 

Attachments

  • Book1.xlsx
    9.6 KB · Views: 0
Thank you for replying! is there anyway to change the highlighted part so I can use validation to select a type?

=INDEX($B$2:$G$40,MATCH($I$3 & $J$2,$B$2:$B$40 & $C$2:$C$40, 0), MATCH($I$2,$B$1:$G$1,0))
 
Hi ,

The problem is that the data in column I does not repeat ; if it repeats , then you can certainly use a DV dropdown for the type , and use the same formula.

Narayan
 
Thanks for your help, I removed the part highlighted in bold and it does most of what I need. Thank you for you help.
 
You must log in or register to reply here.
Back
Top