Hlookup and Match?

[SimonLock]

Hi,

I have attached an example spreadsheet, which shows the numbers I want to be returned (in bordered boxes).

I have a number of lists relating to specific people, which include a date and a name on each row.

I want to find where, for example, Simon is in Charles' list (3rd). I can easily do this by using a match formula.

However, I want excel to lookup the name to find the relevant list (be it Charles, John, whatever), then match the date to return the number 3.

Any ideas?

Thanks in advance.

[Hawkeye16]

I think what you want is an INDEX MATCH combination but I am not sure what you want, even after looking at your attachment. Are you wanting to use the Dates and Names in columns A and B to look up the row in the corresponding table?

[Novice_XL]

In c2

=IF(B2="John",MATCH(1,INDEX(($F$2:$F$5=$A$1)*($E$2:$E$5=A2),0)),IF(B2="Charles",MATCH(1,INDEX(($J$2:$J$5=$A$1)*($I$2:$I$5=A2),0)),""))

Copied down

[SimonLock]

Are you wanting to use the Dates and Names in columns A and B to look up the row in the corresponding table?

Yes, that's correct.

[SimonLock]

In c2

=IF(B2="John",MATCH(1,INDEX(($F$2:$F$5=$A$1)*($E$2:$E$5=A2),0)),IF(B2="Charles",MATCH(1,INDEX(($J$2:$J$5=$A$1)*($I$2:$I$5=A2),0)),""))

Copied down

That works well for those 2, (and is therefore much better than I managed), but I'm wanting this to work if lots more people are added, and this formula requires changing for each added person. Is there any way of making it scalable?

[martindwilson]

=iferror(MATCH(A2,INDEX($E$2:$J$5,0,MATCH(B2,$E$1:$J$1,0)-1)),"") will give those numbers
note: edited range

[Hawkeye16]

I agree with MDW - that is what I would use as well.

[sktneer]

Or you may try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of just Enter.

In C2

Please Login or Register  to view this content.

and copy down.

[Hawkeye16]

sktneer - what is the benefit of that over what MDW provided?

[SimonLock]

=iferror(MATCH(A2,INDEX($E$2:$J$5,0,MATCH(B2,$E$1:$J$1,0)-1)),"") will give those numbers
note: edited range

That's good, any idea why it's not working for the others? (see attachment)

[sktneer]

sktneer - what is the benefit of that over what MDW provided?

His formula (after editing) is simplest one while mine is not.

[martindwilson]

simon is in a1 it needs to be consistent so simon should be in b1

[SimonLock]

simon is in a1 it needs to be consistent so simon should be in b1

Well spotted. Thanks a lot for your help.

[Vikas_Gautam]

Check the attachment...


Say thanks, Click *

[Vikas_Gautam]

Check the attachment...

Say thanks, Click *

Did that helped Simonlock...

Waiting for *