Need help with INDEX/MATCH.

[Janbi]

I'll try to explain as best possible;

For examples sake, lets say we have Worksheet 1 and Worksheet 2. Worksheet 1 has drivers, what vehicles they're driving and what day they're driving. Worksheet 2 has a list of drivers and shows what type of vehicle they're driving and whether they're on holiday or not. Currently this is all manual input.

I need to make it automatic so that Worksheet 2 will scan Worksheet 1 for the drivers by name, see what vehicle they're driving (registration) and pull which type of vehicle it is (from a list, i.e digital, analogue) and put it into Worksheet 2.

How would I go about doing this? I am using Excel 2010.

For some reason I cannot upload an example worksheet.


Please refer to my latest post which has a semi-working formula

[Janbi]

After reading up a bit I'm not sure vlookup is going to be able to achieve this... Any other ideas? This is what I've come up with but I seem to be getting a circular reference warning? =VLOOKUP(Sheet1!$A$2,Monday!$B$3:$K$4,Monday!$A$3:$K$3,FALSE)

[vlady]

Index/match maybe a possibility since you can't upload try it first otherwise you have to upload.

=index(sheet1vehicle typelist,match(sheet2nametolook,sheet1listofnames,0))

to upload click edit on your post,advanced->manage attachments

[Janbi]

I've uploaded an example sheet, had to use the basic uploader; hopefully that might help better explain what I'm trying to achieve.

If you notice M12-M14 is named 'Analogue'. In Sheet 2 I need B2 to scan Sheet 1 for 'Steve' and pull the data in the cell above (it will always be above the name), for example Steve is in C4, I need the data from C3; however, rather than it just pulling "T1" I need it to pull the list name, which is 'Analogue'.

Hope this helps and thank you for your help thus far!

[vlady]

Can you look at this..

maybe you could work on it.

holiday.xlsx

have slow connections here now..

[Janbi]

I need some more help with this, and I have adjusted the thread title now that I know exactly what it is I need doing.

exampleworking.xlsx has the formula in Sheet 2 Column B which is working... For example when you put 'Steve' on Sheet 1 B4 and 'L1' on B3, it will show on Sheet 2 as DIGITAL for Steve etc.. on Master Latest.xls this does not work and as far as I know I have adjusted it right...

The formula on Master Latest.xls is in Sheet1 B2

[Ace_XL]

I'm not entirely sure what you are trying to achieve but looking at both formulas I find a few inconsistencies..

Try this perhaps and let me know if it works

=IFERROR(INDEX(Monday!$G$47:$I$47,MATCH(MIN(IF(Monday!$G$48:$I$68=INDEX(Monday!$B$3:$K$3,MATCH(Sheet1!A2,Monday!$B$4:$K$4,0)),COLUMN(Monday!$G$48:$I$68)-1)),COLUMN(Monday!$G$48:$I$68)-1,0)),"Holiday")

remember to Ctrl+Shift+Enter

[Janbi]

That has fixed it, thank you very much! Can you just point out what the issue was so I can rectify it if it ever happens again?

Also, I need to add mutiple ranges to B3:K3, when I tried to do this it said too many arguments for this function? I need B3:K3, A10:K10, B17:H17 and B24:H24. How do I achieve that?