Match and Index's

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
I have in c/A A3:A109 data that contains station names

I also have in c/B B114:B233 a list of station names, some match c/A's list and some don't

I am trying to create a formula in c/A A114:A233 that will match

1] c/A A3:A109 to c/B B114:B233 and
2] replace with c/B when match is found

I have tried and came up with this it is wrong can anyone help me with this formula:

=MATCH(A3,B114:B233,0)*INDEX(A3,0,1)

Thx

Sean
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
I have not understood completely your requirements.
anyhow I have given a sample srpeadsheet and the formulas in C13 toc17.
does this give anything near to your reqirements. or does this give you some idea how to about to find a solution to your requiremens.
formula in B13 is just copied down

confirm
venkat
Book1
ABCD
1
2
3a
4s
5d
6f
7g
8h
9j
10k
11
12
13aa
14dw
15w#N/A
16e#N/A
17r#N/A
Sheet2
 
Upvote 0
Not sure I understand what you're trying to accomplish. If you get a match between column A and column B, you want to replace the value in column A with the value in column B? But weren't they the same to get a match? So what would replacing the value do?

Or maybe I just didn't read your requirements correctly....
 
Upvote 0
Sorry about the slow response.....holiday!

In the spreadsheet above my objective is to in cell A13 to match A3 with B13 and if successful to insert the letter a, IF ISNA to insert either #N/A or enter 0

Second I would like to Conditional Format this equation so that when ever the match is TRUE to C/F and highlight the cell A13.

This would be useful for me when I have a list of comparable data that I can just insert into column B13 and a formula in A13 with run a match and offset the value if true, then I can run a C/F to highlight the True matches

Hope this makes sense and can be of use,

Thanks a lot guys I appreciate all your help!

Sean
 
Upvote 0
perhaps that would work best! How could I Conditional Format the cells based on the value of the VLOOKUP?

If I write IF(ISNA(VLOOKUP(B13,A$3:A$10,1,0)),0,(VLOOKUP(B13,A$3:A$10,1,0)))

Then I can set the C/F to C/F if value equals 0:

1) How could I create the C/F formula because it would have to be a formula is function in order for me to copy it down the cells from $A13:$A10 or even a macro or else I would have to set the C/F for each cell, which I don't want to do because it would take way too long...

Thanks again

Sean
 
Upvote 0
You could just use the ISNA() part of the formula in the CF. This will give you a TRUE/FALSE result which is what Conditional Formatting needs to trigger the formatting.


e.g. Formula Is: =ISNA(VLOOKUP(B13,A$3:A$10,1,0))


so if the Vlookup result is #NA then the formatting will be "on". If the Vlookup returns a value, no formatting will take place.
 
Upvote 0

Forum statistics

Threads
1,226,454
Messages
6,191,139
Members
453,642
Latest member
jefals

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top