Match and lookup or better

[rogrand]

Hi to all:

I have a spreadsheet with two worksheet:

Data & Helper List

Data:
Market COID
empty cell 10076
empty cell 24548

Helper list:

Market COID
Broward 24548
Dade 10076

What is the most efficient and fast formula that would fill in the
Empty cells from the Helper List I created.


Thank you excel community. This week what would I have done with out you. Been very helpfull.

[The_Snook]

Hi rogrand,

Pop this in cell A2 and copy it down. I've wrapped the IFERROR function around it as not all of the COID numbers have a corresponding match.

=IFERROR(INDEX(Table2[Market],MATCH(Table1[@COID],Table2[COID],0)),"")

Regards,

Snook

[rogrand]

It does insert information but it grabs the first cell and it may not correspond to the COID as it its not really looking up to match it.

[rogrand]

Hi rogrand,

Pop this in cell A2 and copy it down. I've wrapped the IFERROR function around it as not all of the COID numbers have a corresponding match.

=IFERROR(INDEX(Table2[Market],MATCH(Table1[@COID],Table2[COID],0)),"")

Regards,

Snook

It fill in a market, but it takes the first cell and its not looking up and matching.

[Sam Capricci]

probably using an index/match.
something like this...=INDEX('Helper List'!$A$2:$A$1001,MATCH(B2,'Helper List'!$B$2:$B$1001,0)) dragged down.
the misses will show as #N/A so you can add an iferror to it to show mismatches like this...
=IFERROR(INDEX('Helper List'!$A$2:$A$1001,MATCH(B2,'Helper List'!$B$2:$B$1001,0)),"missing")

[rogrand]

probably using an index/match.
something like this...=INDEX('Helper List'!$A$2:$A$1001,MATCH(B2,'Helper List'!$B$2:$B$1001,0)) dragged down.
the misses will show as #N/A so you can add an iferror to it to show mismatches like this...
=IFERROR(INDEX('Helper List'!$A$2:$A$1001,MATCH(B2,'Helper List'!$B$2:$B$1001,0)),"missing")

I have been most successful with your formula, however some item I get a #N/A and in order to get the formula to work, I have to re-type the number it needs to match. Any ideas?

[oeldere]

a2 =Vlookup(B2,'Helper List'!$B$2:$C$1002,2,0)

b2 =20712

in the helper list I duplicate column A in Column C.



Since you added an example (20076) which is not in the list you find #N/A.


See the attached file.

[rogrand]

Why the two Column "Kolom1" and "Market2" and can the "$B$2:$C$1002" be express differently? I tried substituting it for the table name

[Sam Capricci]

Maybe the numbers you are retyping are text. If they are (or any in that column are) try this little trick to convert them to numbers.
Highlight the column, go to the data tab and select text to columns and when it opens if delimited is selected leave that, if it isn't then click that, then just hit finish and it will change any text in the column to numbers.

[oeldere]

@Rogrand

Since you got answers of several forummembers it is good to add to whom you replying.


It does insert information but it grabs the first cell and it may not correspond to the COID as it its not really looking up to match it.

Since you added an example (20076) which is not in the list you find #N/A.

Why the two Column "Kolom1" and "Market2" and can the "$B$2:$C$1002" be express differently?

There is no need for it, but maybe I am old school, but I find the table names annoying.