Problem extracting data from a table when you don’t know where the lookup value is

Topic

New Reply

Hi.

I’ve written many a complicated lookup function in Excel, but the seemingly simple task I am trying to do now has left me stumped.

Attached you will find a workbook. It has two sheets – the first lists a set of public transport services and has a code for the service in column A. The second has a list of composite timetables – multiple services on one timetables – with an ID for the composite in column A again, and a set of values in column E onwards referring back to the individual Service IDs, one cell per service. It is done this way on purpose because the service codes have to be correctly formatted (correct number of spaces) so they are all validation drop-downs with the Sheet 1 service IDs as the validation range.

I now want to insert a column on the first sheet which shows what composite a service ID has been attached to. But that’s the bit that fails. I can look up on a given row, or in a given column, but I want to look up anywhere on the sheet and return the composite ID from column A. Ideally I even want to extend the lookup range, as users will be able to add another composite line. But that’s for the future – I can’t even get it to work on the table I’ve got!

I don’t care about errors where a service ID occurs more than once in different composites – returning just one ID is fine.

Also, it has to be worksheet functions. This will go to a number of users in different areas who will all have different macro pollicies, and is in any case created by code in the first place – so no macro solutions.

Thanks!
Stuart

Reply | Quote

Replies

Stuart, I’m sure you can do what you want with a FIND or FINDNEXT macro. You have provided a sheet but no examples of the end desire.

Are you saying you want to look in all other sheets for the id number in col B and when found put in col c. If so, what if more than one?, If not so, EXPLAIN.

Reply | Quote

I did explain, but happy to make it clearer.

On the attached workbook, look at the “Services” sheet. Cell A18 has the entry “AV 50 ”
Now look at the “Composites” sheet. The reference “AV 50 ” appears in E14, and the ID attached to that row, in A14, is Z284. I want to write that value onto line 18 of the Services sheet, say at N18 not that that matters.

So, to summarise, I want to look up a value from Services!A18 in Composites!E4:O26 and have Composites!A14 returned into Services!N18

And I want to do the equivalent lookup for each value in Services!A4:A155, writing the answer in Services!N4:N155.

Finally, as I said in my original post, it must NOT be a macro.

Reply | Quote

Copy this to your n1>change c! to Composites!>copy down

‘=IF(SUM(INDEX((‘c’!$A$4:$O$500=A4)*(ROW(‘c’!$A$4:$O$500)-ROW(A$1)),)),INDEX(‘c’!$A$2:$A$500,SUM(INDEX((‘c’!$A$4:$O$500=A4)*(ROW(‘c’!$A$4:$O$500)-ROW(B$1)),))),””)

Reply | Quote

I’m seriously impressed! Works a treat – thanks very much!

Now I’m going to have to go away and work out HOW it works!

Reply | Quote